How to Fix Orphaned SQL Users

At my job, we often will take backups or databases and restore them on other servers, for either disaster recovery or for development and testing. We also make use of SQL ID's extensively as these work well with the myriad of different operating systems we support, such as Solaris and Red Hat.

When we restore these databases, the SQL ID's get orphaned, even if they are setup in the main database. What happens is that the main SQL ID still works and the user can be authenticated but the ID's in each database are not attached to the overall ID. In the past, I would delete each account from each database and then from the master ID, re-add each permission, which was a pain.

Fortunately, Microsoft included a stored procedure to re-attach these ID's. All of these instructions should be done as a database admin, with the restored database selected.

First, make sure that this is the problem. This will lists the orphaned users:

EXEC sp_change_users_login 'Report'

If you already have a login id and password for this sqlid, fix it by doing:

EXEC sp_change_users_login 'Auto_Fix', 'sqlid'

If you want to create a new login id and password for this sqlid, fix it by doing:

EXEC sp_change_users_login 'Auto_Fix', 'sqlid', 'login', 'password'

In general, the sqlid and the login are the same thing.