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 command “EXEC sp_change_users_login ‘Report'” is used to generate a report regarding user login information in the SQL Server.

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’.

sp_change_users_login AUTO_FIX, 'DemoLogin4/DemoLogin4' --'LoginName/Username'
Go

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