March 6, 2002 at 3:15 pm
I try to create a new user to a database.
When I create it, I got Error#15023, it says user or role already exists in the current database. But when I click on users icon from this database, I can't see the user name I tried to create. MOre importantly, I can't login to that database with this user name.
Why this happens? And how to get around it?
A quick help will be appreciated.
March 6, 2002 at 3:27 pm
When in doubt run sp_dropuser against the database. Usually you see this when you do sp_attach_db ot restore a backup from another server. The is apparently another process to map the logins back but I don't have it marked here at the house. If you want to see the user just to confirm is there but not recognized in master run "Select * from sysusers" in the database in question and you will see in the name column all the users the database believes are in it.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 6, 2002 at 3:58 pm
SP_Change_users_login is what you're looking for - there are a couple of articles here on the site that discuss it. The ID's are mismatched between sysxlogins in master and sysusers in the db, the proc just fixes them up. Shouldnt have to drop the user, better to avoid if you have any permissions granted to the user (hope not!) they would be lost. If you do wish to remove a user MS recommends sp_revokedbaccess and notes that sp_dropuser is deprecated and actually just calls sp_revokedbaccess now.
Andy
March 6, 2002 at 5:31 pm
Thanks, guys. I think your answers take care of this issue.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply