Fixing Orphaned Database Users

When you migrate a database to a new server (by using BACKUP/RESTORE, for example) the relationship between logins and database users can break. A login has a security-identifier (sid) which uniquely identifies it on the SQL Server instance. This sid is stored for the login’s associated database user in each database that the login has access to. Creating another login on a different SQL Server instance with the same name will not recreate the same sid. The following query demonstrates this link by joining the sys.database_principals system catalog view to the sys.server_principals catalog view on the sid column:

This returns:

LoginName

DbUserName

sid

Veronica

Veronica

0xEC5C372109E10344BC6CE7B04514EC8D

(1 row(s) affected)

If you RESTORE a database from a different SQL Server instance onto a new SQL Server instance— and the database users don’t have associated logins on the new SQL Server instance—the database users can become “orphaned.” If there are logins with the same name on the new SQL Server instance that match the name of the database users, the database users still may be orphaned in the database if the login sid doesn’t match the restored database user sid. To fix this for SQL logins, you can use the sp_change_users_login system stored procedure, which uses the following syntax:

Arguments

Action

If selecting auto_fix, database users and logins will automatically be linked if they have the same name. The report option lists orphaned users in the current database. The update_one option explicitly links a user in the current database to an existing SQL Server login.

User

The database user name.

Login

The SQL Server login name.

Password

This is the password that the new SQL Server login will use if there is no matching login name on the SQL Server instance. This system stored procedure will link those names that match, and create new SQL logins for those orphaned database users that don’t match.

In this example, a database called TestDB was restored to a new SQL Server instance. TestDB has a user named Danny. There isn’t a login named Danny on the new SQL Server instance, so the database user is orphaned. To detect this, sp_change_users_login with the report action is used:

EXEC sp_change_users_login 'Report'

This returns:

UserName

UserSID

Danny

0x87424DA9CF8EF548AE58E19BD04A798D

Next, the sp_change_users_login is executed with the auto_fix action to fix the orphaned database user:

This returns:

Barring a conflict, the row for user 'Danny' will be fixed by updating its link to a new login. The number of orphaned users fixed by updating users was 0.

How It Works

This recipe demonstrated how to use sp_change_users_login to fix orphaned database users. The first query executed the procedure with the Report option in order to show any orphaned users:EXEC sp_change_users_login 'Report' After that, the stored procedure used the Auto_Fix option to link a specific database user (Danny):EXEC sp_change_users_login 'Auto_Fix', 'Danny', NULL, 'newDannypassword!#@' A password was provided in the fourth parameter so that if an existing login named Danny isn’t found, it will be created and will use the provided password. The results of that operation tell us that no existing logins were found (“the number of orphaned users fixed by updating users was 0”). Instead, a new login was created (“the number of orphaned users fixed by adding new logins and then updating users was 1”). The number of orphaned users fixed by adding new logins and then updating users was 1.