Techtip: Reassociating an orphaned user after Database move

Every database on a Microsoft SQL Server instance maintains its own Access Control List (ACL) with a list of the users which have rights on the database, and what rights they have. This ACL however, doesn’t contains only contain user names, but also the Security Identifier (SID) of the user. This means that when moving a database, you can’t simply create a user on the instance you’re moving the database to and expect it to have the same rights. It won’t, because the SID of that user will be different, even if the user name is the same.

Microsoft has hedged against this, and allows you to update the ACL on the database by associating the user name in the ACL with the SID of the user with the same name on the instance to which the database has been moved. By doing this, you don’t have to manually delete the user permissions from the database security tab and set them up again. You can simply run a stored procedure.

To check whether or not there are any orphaned users in your database ACL, run this command on the database:

This will list any orphaned users with rights on the database.

To reassociate the users with a valid SID and keep it’s ACL entries on the database, run the following query:

After running the last command, your user rights will be correct for that user. You can test by running the first command again. No entry for that user should show up.

NB. The square brackets <> can be removed.

Leave a Reply