Find Orphaned Users In SQL Server
Orphan users can occur once you detach databases or restore a database from another SQL Server database instance. The reason for this is that whenever a user is created, a login name and SID will be created. Each database user is mapped with an SID. This SID is different from one SQL Server instance to another. So in case, you restored your databases from some another SQL Server instance, the SID in the database will match with the SID of the login in the current server. You can view un-matching users from following code:
EXEC sp_change_users_login 'REPORT'
So you can match them by providing following script.
EXEC sp_change_users_login 'Update_one',@UserName,@UserName
Also, you can use following script from SQL Server 2005 SP2 onwards.
ALTER USER dbusername WITH LOGIN = Loginname
However, what if you have numerous users in your database? Running the script for each user may be a tedious task. The following script will automatically highlight the orphan users. However, this script will assume that database user and login names are the same.
/* Author: Dinesh Asanka Purpose: Fixing orgphan users Date: 2012-07-07 Warning: Author is not liable for any after issues with this script */ SET NOCOUNT ON CREATE TABLE #orphan_users (UserName varchar(50), UserSID varchar(1000) ) INSERT INTO #orphan_users EXEC sp_change_users_login 'REPORT' ALTER TABLE #orphan_users DROP COLUMN UserSID ALTER TABLE #orphan_users ADD IsProcessed tinyint Declare @UserName varchar(20) WHILE (SELECT COUNT(1) From #orphan_users Where IsProcessed IS NULL ) > 0 BEGIN SELECT Top 1 @UserName = UserName From #orphan_users Where IsProcessed IS NULL Update #orphan_users SET IsProcessed = 0 WHERE UserName = @UserName EXEC sp_change_users_login 'Update_one',@UserName,@UserName END DROP TABLE #orphan_users
Can we avoid this? Rather than allowing an SID to be automatically assigned, you can provide it:
CREATE LOGIN loginname WITH PASSWORD = 'Pa$$w0rd', SID = 0x4B96EFEDC3BD324BB528A387B15839BF
In above case, all your servers will have the same SID for users and restoring databases across SQL Server instances won’t be an issue.