Re-Associate An Orphaned SQL User With SQL Login

If you ever have the need to move a SQL Server Database from one server or instance to the next then you may come accross orphaned SQL Users.  e.g.  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 re-produce (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.

Advertisements

One response to “Re-Associate An Orphaned SQL User With SQL Login

  1. Your the Man!!! thank you it worked

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s