Re: Can't login to SQL Server

Klerman Gutierrez (mcs@xxxxxxxxxxxxxxxxxxxxxxxx) writes:
The SQL than I'm using is this:

exec sp_addlogin 'USERA', 'xxxxx', 'MyDatabase'
exec sp_addsrvrolemember 'USERA', sysadmin
exec sp_addlogin 'USERB', 'xxxxxx', 'MyDatabase'
EXEC sp_grantdbaccess 'USERB'
EXEC sp_addrolemember db_datareader, 'USERB'
EXEC sp_addrolemember db_datawriter, 'USERB'

Have you checked the output from this script? From your description I
would expect this command to fail:

EXEC sp_grantdbaccess 'USERB'

There is already a USERB in the database per your description. The
database user is part of the database, so it was included when you copied
the database.

What you need to do is to connect the existing database user USERB
with the newly created login USERB. (The logins are stored in master,
so the login USERB was not included in the copy operation.) You can
do this with the command:

ALTER USER USERB WITH LOGIN = USERB

If this gives you a syntax error, it is because you have not applied SP2
of SQL 2005, which you should do.

There is one more thing you need to be aware of: in SQL 2005, passwords
are always case-sensitive. So if the script says:

exec sp_addlogin 'USERB', 'TopSecret', 'MyDatabase'

and you try to log in with "topsecret" that will fail.

Finally, while the above commands work on SQL 2005, SQL 2005 now has
proper commands to create logins and user, CREATE LOGIN and CREATE USER.
There are no commands for adding users to roles though.



--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.

Relevant Pages

  • Re: Cant login to SQL Server
    ... exec sp_addlogin 'USERA', 'xxxxx', 'MyDatabase' ... exec sp_addlogin 'USERB', 'xxxxxx', 'MyDatabase' ... EXEC sp_grantdbaccess 'USERB' ...
    (microsoft.public.sqlserver.security)
  • Re: Changing DB Owner
    ... specified login must not already be a user in the database. ... USE MyDatabase ... EXEC sp_changedbowner 'SQLAdmin' ... SQL Server MVP ...
    (microsoft.public.sqlserver.server)
  • Re: Import Logins from text or spreadsheet
    ... @SomeLogin sysname, ... EXEC sp_addlogin $L$, $P$, $D$ ... >Let's assume your Excel spreadsheet has 3 columns: Login, ... One method is to generate the needed script using SQL and ...
    (microsoft.public.sqlserver.server)
  • SQL-Server 2005: Login name as variable?
    ... DECLARE @sql nvarchar ... The login name even as variable. ... '@sql' ist kein gültiger Windows NT-Name. ... A other variante with exec: ...
    (microsoft.public.sqlserver)
  • Re: Problem with Search engine access?
    ... present user account" typically indicates that the SQL login ... removed or altered the SQL Server login BUILTIN\Administrator? ... exec sp_defaultdb N'NT Authority\System', N'master' ...
    (microsoft.public.sqlserver.fulltext)