Identify and fix orphan users in SQL Server

In this post I would like to focus on identifying orphan users and fix them in sql server. We have a need for this when we restore a database from a one instance to a different instance. In order to list the users along with their Security Identifiers aka SID in a database that is not associated with any login. We need to pass the parameter ‘Report’ to the SP_Change_Users_Login stored proc.


sp_change_users_login @Action='Report';

TO fix the orphan user issue we need to relink the  server login account specified by <login_name> with the database user specified by <database_user> and this can be achieve with the following sql.

sp_change_users_login @Action='update_one', @UserNamePattern='<database_user>',
 @LoginName='<login_name>';

Once the above query is executed the user should be able to access the database.

Hope this helps.

Advertisements
This entry was posted in SQL Server DBA Stuff and tagged , , . Bookmark the permalink.

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