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.
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.
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:
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’.
To actually fix orphaned users, you can use the
Auto_Fix option like this:
'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:
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’.
At last check the orphaned again by using the below command. If orphaned users fixed successfully, then we will not get any orphaned users.