How Do I Reassociate an Orphaned SQL User with SQL Login?

Answer

If you ever have the need to move an SQL Server Database from one server or instance to the next then you may come across orphaned SQL Users, for example, the User is present in the database, but it is no longer associated with the SQL Login on the Server.

When that happens you have a couple of options.

You can delete the database user, re-add them, re-grant the appropriate role or rights, and be on your way.

It gets a bit more complicated, though, when the user owns schema(s) in the database.

If you try to delete and re-add them you’ll receive an error similar to the following:

"Drop failed for User 'sqlusername'.
An exception occurred while executing a Transact-SQL statement or batch.
The database principal owns a schema in the database, and cannot be dropped."

In order to get around that error, you would have to temporarily re-assign the owner of the schema(s) to another user in the DB before being able to delete their User Login.

In addition to the schemas the user may own, they may have been added to roles within the database, they may even have been granted explicit permissions that you would then have to manually reproduce (If you know what they were).

If that’s ‘the case, recreating those rights can be a meticulous task.

A much better alternative is to execute a statement very similar to the one shown below:

EXEC sp_change_users_login 'UPDATE_ONE' , 'sqlusername', 'sqlloginname'

It will re-associate the SQL User with the SQL Login and the Schema(s), Roles, and Permissions are retained.