Orphaned Database users (issues and solutions)

There are several situation where the database user mapping will be lost to sql logins.

• sometimes, after a Database is restored from backup or across sql servers.
• After the Logins are moved across SQL Servers.
• After Dropping and re-creating a SQL Server Login.
• After Re-naming and SQL Server Database User / Server Login.

Out of this the most common scenario is the Database Restore and Move Logins.

Now the IMP part is, How to re-do the mapping between the Database Users and Logins.
Praise BOL: Microsoft has included a stored procedure for Just that.

sp_change_users_login (members of db_owner fixed database owner can execute this sp)

Lets look at the magic and logic of using this SP. Here are some simple steps to perform, before going further.

use <Database>;
sp_change_users_login ‘report’ : This will report and orphaned users in the database.

sp_change_users_login ‘update_one’,’<user>’,’<user>’  : This will re-map/correct the mapping of the user vs Login. you can map the existing database user to a new SQL Server Login (sp_change_users_login ‘update_one’,’user’,’newloginuser’) or Same Login as above.

The UPDATE_ONE : Links the Specified (one user at a time) user to a existing login or new login.

Instead of UPDATE_ONE, you can use ‘AUTO_FIX’ :  Which will help map the Database user to same login or new logins with password, if the login does not exist.

sp_change_users_login ‘Auto_Fix’,’dbanation’,NULL,’passw0rd’ : This will create a Login based on the database user with the password.

What do you do if there are hundreds of user in the database.? Well something like this, while using the RESULTS to TEXT option in SSMS.

select ‘EXEC sp_changeuserslogin ‘++””+’update_one’+””+’,'+””+u.name +””+’,'+””+u.name+””+CHAR(10)+’ GO’ from master..syslogins l
right join sysusers u
on l.sid = u.sid
where l.sid is null
and issqlrole <> 1
and isapprole <> 1  
and ( u.name <> ‘INFORMATION_SCHEMA’ and u.name <> ‘guest’  and u.name <> ‘system_function_schema’ )

The Results to TEXT option will output a proper statement with the GO Clause in a new line. (This will have no affect while in Grid View)

This will generate the EXEC Statements for all the users with update_one clause. Copy and paste this in another Query window and Execute and Enjoy the Time Saved: