Archive for the ‘Orphaned Users’ Category

How to resolve orphaned users?

Your Development team has submitted a ticket for you to restore a number of databases to the Development environment as they wish to have the most recent data for testing purposes. You have the backup files ready and copied over to the specified Development servers for restore. As per your past experiences, you had expected that the orphaned users will be a problem. You want to do the preparation work for a smooth process of backup, restore, and then resolve the orphaned user issues. What can you do?

For starters, we need to know what is an orphaned user and how does a user become an orphaned user. Simply put, an orphaned user is a user without an associated login. As we all know, you need to have a login to access SQL Server and you need to map a login to a user for a corresponding database in order for it to have access to the database. When you create a login, it will be recorded in the sys.server_principals system view of the master database. When you create a user, it will be recorded in the sys.database_principals sytem view of the corresponding user database. These two system views are related together by the SID column. SID stands for Security ID. If the SID in the sys.database_principals system view of a user database does not match the SID in the sys.server_principals system view in the instance level, then the “orphaned user” symptom has occurred.

Not to over complicate this process or making it sound more difficult than it is. Essentially, the process of resolving orphaned users is updating the SID column of the sys.database_principals system view to match the SID column of the sys.server_principals system view of the master database. Yes. It is really that simple and you can definitely do it.

Orphaned users are usually caused by:

  • Database restores to separate SQL Server instances.
  • Accident deletion of logins.

You can resolve the orphaned users issue by:

  • Mapping them to valid logins.
  • Deleting them, creating logins, and then mapping the logins to the database users. This is not recommended as you will lose the associated permission in the corresponding databases.

In the following steps, we will test resolving the orphaned users issue by:
1. Create the sp_help_revlogin stored procedure in the master database provided by Microsoft.
2. Script out the logins being used by the user database.
3. Backup the user database.
4. Restore the user database to a separate instance of SQL Server.
5. Run the script you had created in step 2 to create the logins in the instance you had just restored the database.
6. Identify the orphaned users after the restore.
7. Resolve the orphaned users.
8. Verify if the orphaned users have been resolved. Remove the orphaned users from a different domain if necessary.

Implementation steps:

1. Create the sp_help_revlogin stored procedure in the master database provided by Microsoft. This stored procedure will be used to transfer the logins and passwords between instances.
Rather than posting the T-SQL code here, I would recommend you go to http://support.microsoft.com/kb/918992 to copy the T-SQL code for the sp_hexadecimal stored procedure as it may be subjected to change as Microsoft sees fit.

2. Script out the logins being used by the user database to be restored over to a separate instance of SQL Server. Copy the output and save it to a file to be executed in the separate instance of SQL Server where the database will be restored to.

-- Script out the logins used by the user database by calling the sp_help_revlogin stored proc

SET NOCOUNT ON

USE Adventureworks -- replace with your user database name
go

-- declare the required variables
DECLARE @login_name VARCHAR(123) -- to store the login names
, @id TINYINT -- to store the sequence id of the logins
, @max TINYINT -- to store the number of logins

-- create a temp table to store the logins
IF OBJECT_ID('TEMPDB..#logins') IS NOT NULL
DROP TABLE #logins

CREATE TABLE #logins
(
id int identity (1,1)
, login_name VARCHAR(123)
)

-- load the logins into the temp table
INSERT INTO #logins
SELECT
    l.name
FROM
    sys.database_principals u INNER JOIN master.sys.server_principals l 
    ON u.sid = l.sid
    WHERE l.name <> 'sa'

-- set the initial value for the id to start and find the max # of logins to process
SELECT @id = 1
, @max = max(id)
FROM #logins    

WHILE (@id <= @max)
BEGIN
	SELECT @login_name = login_name
	FROM #logins
	WHERE id = @id
	
	-- script out the login and the password
	EXEC sp_help_revlogin @login_name
	
	SET @id = @id + 1
END

3. Backup the user database.

-- Backup the user database
backup database AdventureWorks -- replace with your database name here
to disk = 'C:\AdventureWorks\Backups\aw.bak' -- replace with your backup path here

4. Restore the user database to a separate instance of SQL Server.

-- Restore your database to a separate instaince of SQL Server

-- Determine the backup file info
RESTORE FILELISTONLY 
FROM DISK = 'C:\AdventureWorks\Backups\aw.bak' 

-- Restore your database
-- Make sure you have enough disk space before starting
RESTORE DATABASE AdventureWorks -- replace with your database here
FROM DISK = 'C:\AdventureWorks\Backups\aw.bak' -- replace with your backup path here
WITH MOVE 'AdventureWorks_Data' TO 'C:\aw\AdventureWorks_Data.mdf' -- replace with your data file destination here
, MOVE 'AdventureWorks_Log' TO 'C:\aw\AdventureWorks_Log.ldf' -- replace with your log file destination here

5. Review the script for the logins you had created in step 2. If you have restored the database to a separate domain, you will need to comment out the logins for the previous domain. Then, run the script. Note: if the logins had already existed, you will get the error that “The server principal ‘Login Name’ already exists. But, the logins that do not exist will get created.

6. Identify the orphaned users after the restore.

-- Identify the orphaned users
USE AdventureWorks -- replace with your database name here
go
sp_change_users_login 'report'

7. Resolve the orphaned users. What this essentially means is that the sid column in the sys.database_principals system view of the corresponding user database will be updated to match the same as the sid column in the sys.server_principals system view of the master database.

-- Resolve the orphaned users
USE AdventureWorks -- replace with your database here
go

-- declare the required variables 
DECLARE @user_name VARCHAR(123) -- to store the orphaned user names
, @id TINYINT -- to store the sequence id of the orphaned users
, @max TINYINT -- to store the number of orphaned users

-- create a temp table to store the orphaned users
IF OBJECT_ID('TEMPDB..#orphaned_users') IS NOT NULL
DROP TABLE #orphaned_users

CREATE TABLE #orphaned_users
(
id int identity (1,1)
, user_name VARCHAR(123)
, user_sid VARCHAR(123)
)

-- load the orphaned users into the temp table
INSERT INTO #orphaned_users
EXEC sp_change_users_login 'report'

SELECT @id = 1
, @max = MAX(id)
FROM #orphaned_users

WHILE (@id <= @max)
BEGIN
	SELECT @user_name = user_name
	FROM #orphaned_users
	WHERE @id = id
	
	-- if you already have a login id and password for this user
	-- assuming the user login is the same as the login name
	EXEC sp_change_users_login 'Auto_Fix', @user_name
	
	-- map an existing user in the database to a login
	-- EXEC sp_change_users_login 'Update_One', 'DatabaseUser', 'Login'
	
	-- if you wish to create a new login id and password for the user
	-- EXEC sp_change_users_login 'Auto_Fix', 'DatabaseUser', 'Login', 'password'
	
	SET @id = @id + 1
END

8. Verify if the orphaned users have been resolved. Remove the orphaned users from a different domain if necessary.

-- Verify if there are still any orphaned users
USE AdventureWorks
go
EXEC sp_change_users_login 'REPORT'

-- Drop the orphaned users from a different domain if necessary
USE AdventureWorks
go
DROP USER user_name -- replace with the domain login from a different domain

REFERENCES:

SIDs and IDs
http://msdn.microsoft.com/en-us/library/ms403629(v=sql.105).aspx

sys.server_principals
http://msdn.microsoft.com/en-us/library/ms188786(v=sql.105).aspx

sys.database_principals
http://msdn.microsoft.com/en-us/library/ms187328(v=sql.105).aspx

How to transfer logins and passwords between instances of SQL Server
http://support.microsoft.com/kb/918992

sp_change_users_login
http://msdn.microsoft.com/en-us/library/ms174378(v=sql.105).aspx