There are several instances when you may encounter SQL Server errors indicating that a user or role already exists. This is likely after restoring a database from another server without a master DB. When you attempt to add a login or to set permissions, you receive an error stating that the user or role already exists. In many cases, you are unable to drop the login or role without going into system tables.
But wait! There is a better and cleaner way, one that not too many people seem to know about.
Last week, I showed a colleague how to do this and he said: "Wow, that's one of the more magical things I have seen in SQL Server." Okay, maybe it just seems magical because very few have ever seen it done. Anyway, here's the scoop:
There are two steps:
1. Run the sp_change_users_login procedure with the 'Report' option in the database(s) where you are experiencing the issues. For example:
EXECUTE sp_change_users_login ?Report?
The results of the procedure should name the login you are experiencing the issues with, as well as any other logins you are having trouble with inside of that database.
2. Next, run the same procedure with the 'Auto_Fix' option and pass in the LoginName(s) you would like to resolve. You will need to run this for each LoginName that is affected.
EXECUTE sp_change_users_login 'Auto_Fix', 'LoginName'
This procedure should resolve any discrepancies with the login or role. To verify resolution you can run the procedure in Step 1 to ensure no login names are returned once this step is complete.
That's it! I hope you find this as helpful as I have.
For More Information
- What do you think about this tip? E-mail us at editor@searchDatabase.com with your feedback.
- The Best SQL Server Web Links: tips, tutorials, scripts, and more.
- Have an SQL Server tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical SQL Server questions--or help out your peers by answering them--in our live discussion forums.
- Check out our Ask the Experts feature: Our SQL, Database Design, Oracle, SQL Server, and DB2 gurus are waiting to answer your toughest questions.