Fixing Orphaned Users in SQL Server

A ‘Login’ allows us to connect to the SQL Server and a ‘User’ allows us to connect to a database within the SQL Server.’ Logins’ are created at the server level and ‘Users’ are created at the database level. To connect a Database of a SQL Server we need a login and a user that must be mapped with the login.

In simple language, we may say that ‘Login’ is required to enter in a SQL Server then the same ‘Login’ must be mapped with a ‘User’ of a Database to enter the Database. To access any objects in the database, we must have a login that’s mapped to a user in the database, and that the user must be granted appropriate rights in 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.

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

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

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

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

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


Comments

Leave a Reply

Your email address will not be published. Required fields are marked *