SQL Server Tip - Fixing Orphaned Users
How often do you bump into problems with users and security when you move a SQL Server database to another server. The most annoying problem is when you try to create a user under Security, and the user exists within the moved database. When you try to apply user mapping and permissions you get an error like this:
Error 15023: User, group or role '%s' already exists in the current database.
Normally you don't have to reapply the user permissions because SQL Server uses a special background process to associate user accounts in a database with logins on the server. This could take some time, but luckily there’s a quick way to do this:
use database
go
sp_change_users_login 'auto_fix', 'UserName'
This will output the following result.
The row for user ' ' will be fixed by updating its login link to a login already in existence.
The number of orphaned users fixed by updating users was 1.
The number of orphaned users fixed by adding new logins and then updating users was 0.
Microsoft has a knowledge base article that explains the problem and solution Q240872.
Technorati tags:
SQL Server