Fixing Orphaned Users in SQL Server

Introduction

In easier terms, a ‘Login’ is like an entry pass to get into a SQL Server, and a ‘User’ is like an entry pass to access a specific database within the SQL Server. Logins are made at the server level, while Users are created at the database level. In order to connect to a database in a SQL Server, we need both a login and a user that are linked together.

To put it simply, think of a ‘Login’ as the key to enter the SQL Server, and that same ‘Login’ needs to be connected to a ‘User’ in a specific database to actually enter and use that database. To be able to work with any items or information inside the database, we need a login that is linked to a user within the database. Additionally, the user must be given the appropriate permissions or rights within the database.

Scenarios

1) When the database is moved or backup and restore to another server, users within the database moved to the new server but its associate login does not exist in the destination server.

2) If any login is deleted from the Master database but still user is still available in the database.

Find the orphaned users

To find the orphaned users in SQL Server we may use below TSQL commend. Microsoft Ref.

The sp_change_users_login system stored procedure in SQL Server is used to fix orphaned users. Orphaned users are database users that are not mapped to a SQL Server login. These can occur when a database is restored or detached/attached, and the user in the database is not linked to a login at the server level.

The command you provided:

EXEC sp_change_users_login 'Report'

Fixing Orphaned Users

If the ‘User’ already exists in the database and a new ‘Login’ needs to create then follow the below steps.

Use existed SID from ‘User’ to create a new login

USE
MASTER
GO
CREATE LOGIN [DemoLogin3] WITH PASSWORD = 'Welcome@12345',
SID = 0x7D3A3F262A4E7343B5F8EC53FA128AA5 -- SID of DemoLogin3
GO

If the ‘User’ and ‘Login’ already exist with different SID, then follow the below steps to map ‘Login’ and ‘User’.

sp_change_users_login UPDATE_ONE, 'DemoLogin4', 'DemoLogin4'
GO

If the ‘User’ and ‘Login’ already exist with the same SID, then follow the below steps to map ‘Login’ and ‘User’.

To actually fix orphaned users, you can use the Auto_Fix option like this:

EXEC sp_change_users_login 'Auto_Fix', 'username'

Replace 'username' with the actual username of the orphaned user you want to fix.

Keep in mind that sp_change_users_login is a deprecated feature in SQL Server, and it’s recommended to use ALTER USER to map users to logins in modern SQL Server versions. For example:

ALTER USER username WITH LOGIN = loginname

This above command maps the database user ‘username’ to the SQL Server login ‘loginname’.

If the ‘User’ and ‘Login’ already exist with the different SID but with the same name, then follow the below steps to map ‘Login’ and ‘User’.

sp_change_users_login AUTO_FIX, 'DemoLogin4', NULL, 'Welcome@1234'
-- Login name & User name is same, DemoLogin4
GO

At last check the orphaned again by using the below command. If orphaned users fixed successfully, then we will not get any orphaned users.

EXEC SP_CHANGE_USERS_LOGIN 'REPORT'
GO