SQL Server Permissions - Fixing Orphaned Users

Introduction

This short article is on recovering orphaned users when moving a database from one server to another. It took me quite a while to find a good fix, and once I had, it seemed only fair to share it with fellow CPians:

Sometimes it is necessary to move a database from one server to another. In my case we decided to place a "disaster recovery" machine in a building in another city, and copy various things, including our database across every night.

This works great, but we couldn’t use the website which is dependant on the database on this new server. The ASP error coming back was that the database required could not be opened.

The Problem

On the "Security" tab of our SQL server, the appropriate user existed. But when I went into the individual databases and looked inside the "User" tabs, the users (which had been there yesterday, before the database was copied during the night) were no longer there. "Not a problem", I thought – right click, "New Database User", select the user and click OK. At this point, the user was not added, but the following message was displayed:

"Error 15023: User or role '%s' already exists in the current database."

Rather annoying, since the user was clearly not listed.

After some reading, I found that the problem occurs as each user name is stored in the syslogins table with a unique "SID" (for SQL 2000 – SUIDs for SQL 6.x or earlier) on each server. When a database is copied to a new server the SIDs for the usernames no longer match.

The fix I found is as follows:

1. Open "Query Analyser".
2. Run the following stored procedure:

   "sp_change_users_login 'auto_fix', 'UserName'"

   where UserName is the user name you need fixed.

3. Go back to "Enterprise Manager", right click on the database and select "Stop". Once it’s stopped, right click again and select "Start".
4. Return to the “Users” tab on your database – I found it wasn’t necessary to re-add the user at all – it had been added already.

* Note that the execute permissions will need to be reset for any stored procedure called using this username.

Well, that's the problem and the fix as I found it. Hopefully this will help to make someone else's day less bewildering than my morning was.

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here