Fixing Orphan Users in SQL Server

In this article we will see and learn How to discover and handle orphaned database users in SQL Server.

Orphan users

These are users which are available in the database level but their mapped logins not available in the server level. Orphan users are created when a database is restored from a backup of one db server on another db server.

Problem

Attaching and restoring databases from one server instance to another are common tasks executed by a DBA. After attaching or restoring of a database, previously created and configured logins in that database do not provide access. When you restore a Microsoft SQL Server database on a different machine, you cannot access the database until you fix the permissions.

The problem is that the user in the database is an “orphan”. This means that there is no login id or password associated with the user. This is true even if there is a login id that matches the user, since there is a SID that does not match.

Solution

In order to get this problem fixed, Follow below steps as per your requirement:

To get the Orphan users in any database in SQL Server use below :

Syntax :

USE DATABASENAME
EXEC sp_change_users_login report
GO

Example :

Let us assume we have restored TestDb from Server1 to Server2, using below command in Server2.

USE TestDb
EXEC sp_change_users_login report
GO 

Output :

UserName UserSID
Test1    0x7A4X871C3EXX7C42X67B5F3CD2C35FXX
Test2    0x7A4E871C3EXX27C42B67XXF3CC4C35FXX
Test3    0x7A5E871X3EXX27C42F57XXF3CC4C35FXX
Test4    0x7A5E871C3EFF27C32D67XXF3CC4C45FXX

Below methods could be used to fix Orphan users.

1. USING WITH ORPHANED USER SID : To fix any orphaned users, use create login by using SID.

Syntax :

USE MASTER
CREATE LOGIN [LoginName] 
WITH PASSWORD = 'Password',
SID = 0x7A4X871C3EXX7C42X67B5F3CD2C35FXX 

Example –

USE MASTER
CREATE LOGIN [Test1] WITH PASSWORD = 'Pa$$W0rdT1', 
SID = 0x7A4X871C3EXX7C42X67B5F3CD2C35FXX 

2. USING UPDATE_ONE : UPDATE_ONE could be used to map even when Login name and User name are different or could be used to change user’s SID with Logins SID.

First, create new login.

USE MASTER
CREATE LOGIN [LoginName] WITH PASSWORD = 'Password'

Once login is created use UPDATE_ONE to fix orphan user.

Syntax :

USE DATABASENAME
sp_change_users_login UPDATE_ONE, 'UserName', 'LoginName'
GO

Example :

USE MASTER
CREATE LOGIN [Test2] WITH PASSWORD = 'Pa$$W0rdT2'
USE TestDb
sp_change_users_login UPDATE_ONE, 'Test2', 'Test2'
GO

3. USING AUTO_FIX TYPE 1 : When Login Name and User Name are same.

First create the login and then assign login SID to Orphan User.

Syntax :

USE master
CREATE LOGIN [LoginName] WITH PASSWORD = 'Password'

USE DATABASENAME
sp_change_users_login AUTO_FIX, 'LoginName/UserName'
Go

Example :

USE master
CREATE LOGIN [Test3] WITH PASSWORD = 'Pa$$W0rdT3'
USE TestDb
sp_change_users_login AUTO_FIX, 'Test3/Test3'
Go 

TYPE 2 : Without creating the login.

Syntax :

USE DATABASENAME
sp_change_users_login AUTO_FIX, 'UserName', NULL, 'Password'
GO

Example :

USE TestDb
sp_change_users_login AUTO_FIX, '4', NULL, 'Pa$$W0rdT4'
GO 

To get the Orphan users in any database after using above methods :

USE TestDb
EXEC sp_change_users_login report
GO 

Output :

Once the orphan users are fixed successfully, there will not be any orphan user (UserName and UserSID) as the result of above command.

That's all in this post. If you liked this blog and interested in knowing more about SQL Server, Please Like, Follow, Share & Subscribe to www.ImJhaChandan.com .