Error 21002 when creating a new user -- sql server new users

sql server new users : Error 21002 when creating a new user

jayharris NO[at]SPAM gmail.com
9/14/2006 8:16:00 AM

I'm trying to create a new user for my MSSQL database through the
Enterprise Manager. In the SQL Server Login Properties dialog box, I
type in for Name: "Ken", I choose SQL Server Authentication (Windows
Authentication won't work) and type in my password. For the default
Database, I choose "KenJackson" and then in Database Access, I check
off the Permit box next to where 'KenJackson' is listed. I click OK and
I'm asked to confirm my password. Then, the dialog box doesn't close
but I can see the Ken user listed with the rest of the logins, and then
I get the following message: Error 21002: [SQL-DMO]User 'Ken' already
exists. If I click OK on the error message and Cancel in the SQL Server
Login Properties dialog box, the Ken user is still listed, but when I
check the Database Access of that user, Permit isn't checked off. Does
anybody know what I'm doing wrong?

As a little bit of context, the reason I'm doing this is because when
my C#/ASP.NET application first calls SqlConnection.Open, it throws an
exception whose Message is "Cannot open database requested in login
'KenJackson'. Login fails.\r\nLogin failed for user 'Ken'." I don't
know too much about MSSQL, so I might not even be asking the right sort
of question.

Thanks.

Jens
9/14/2006 10:26:35 AM

You will need to grant the uset the appropiate permissions on the
database to access it, otherwise he will be rejected during the login.
if you are not sure, set the database to master first which should be
accessible to him as a authenticated user in SQL Server.

HTH, jens Suessmeyer.

---
http://www.sqlserver2005.de
---

Warren Brunk
9/14/2006 10:37:58 AM

You can fix this with the system stored procedure sp_change_users_login.
Try:
EXEC sp_change_users_login 'AutoFix', 'Username' on the specific Database.

BOL
Article...http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ca-cz_8qzy.asp

If that doesnt work then...

First check sp_helpuser to see if it is present.
sp_dropuser
sp_adduser
--
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server new users

home · blog · groups

Questions? Comments? Contact the dn