How to Identify and Fix Orphaned Users in SQL Server

by Manzoor Siddiqui · Published July 2, 2015 · Updated September 24, 2015

300views

We know about logins and users in SQL Server, to connect to instance and database we have to go through a defined principals. This principal is defined by Microsoft SQL Server to authenticate and verify the login credentials and decides whether to allow access or not based on certain defined criteria. You can query and find the login details in sys.server_principals and sys.syslogins views.

Sometimes when we try to login to newly migrated database or new restored database we get authentication issue. Basically, SQL Server login is mapped to database user and if this mapping is broken or it is not correctly defined as per SQL Server pricipals then login will not be successful for that particular user of database on particular instance and this user is called orphaned user. Also if security identifier is not available for particular database user in server instance, in that case also user becomes orphaned. If you will query sys.server_principals and sys.syslogins you will get some useful information but it is insufficient details for login as login password is not in actual format. So here we are going to find out orphaned users and techniques to map those users with correct security identifier in an instance.

If we refer sys.server_principals and sys.syslogins view it will give you below details.

SELECT name, principal_id, default_database_name, default_language_name, type_desc
FROM sys.server_principals WHERE type_desc = 'SQL_LOGIN'
GO
SELECT loginname, dbname, password, language FROM sys.syslogins
WHERE password IS NOT NULL
GO

How to find Orphaned Users

I have restored my database ‘ClientInfo’ from one server to another server and I am going to check if there is any orphaned user available. We can execute below query on query editor window for particular database which we have restored or attached.

USE ClientInfo
GO
sp_change_users_login 'Report'
GO

So here we have two orphaned users ‘sa’ and ‘Lisa’ and their username and SID is given side by side . We will use this SID to map and fix the orphaned users.

How to Fix Orphaned Users

We will cross check details by following query for Login SID and User SID differences and we will map it by query. As we can observe for login ‘Lisa’ there is a difference in Login SID and User SID.

USE ClientInfo
GO
SELECT name "LoginName", sid "LoginSID" FROM sys.syslogins WHERE name = 'Lisa'
GO
SELECT name "UserName", sid "UserSID" FROM sys.sysusers WHERE name = 'Lisa'
GO

If you will try to login with login ID ‘Lisa’, you will get authentication failure error. So to fix this login we will use below query because SQL Server login name and user name is same so we can map it easily.

USE ClientInfo
GO
EXEC sp_change_users_login 'Auto_Fix', 'Lisa'
GO

Now again we will execute the query to check orphaned user details. So now login ‘Lisa’ is fixed and we can login successfully.

USE ClientInfo
GO
sp_change_users_login 'Report'
GO

Now consider different scenario, if you have restored the database but login is not available in the instance then you have to create the same to map with database user, You can do the same by executing below query.

USE ClientInfo
GO
EXEC sp_change_users_login 'Auto_Fix', 'Lisa', NULL, 'B@6P@$$w0r6'
GO

where Auto_Fix maps the SQL Server login with the records available in the sys.database_principals view.

above query will check user ‘Lisa’ if it doesn’t exist then it will create a new one with password B@6P@$$w0r6.

If you want to map a database user with new login, you can do the same by following query.

--Create Login
CREATE LOGIN LisaNew WITH PASSWORD = 'N3wP@$$w0r6'
GO
--Map User Lisa to Login LisaNew
USE ClientInfo
GO
EXEC sp_change_users_login 'Update_One', 'Lisa', 'LisaNew'
GO

Here Update_One maps the user to existing SQL Server login. You have to first create the login then map the user to created login.

Kindly note that Microsoft has included sp_change_users_login to deprecated features after SQL Server 2012 and asked to use ALTER USER feature in future development works.