Click here to Skip to main content
Click here to Skip to main content

SQL Server Permissions - Fixing Orphaned Users

, 4 Nov 2003
Rate this:
Please Sign up or sign in to vote.
A short article on recovering orphaned users when moving a database from one server to another.

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.

License

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

About the Author

Megan Forbes
Web Developer
Australia Australia
I am a South African web / SQL developer currently spending my time as a mum. My other passion is photography and have put a small portfolio up here[^]

Comments and Discussions

 
GeneralBrilliant, cheers PinmemberPGMIKET28-May-07 18:13 
JokeThanks, Megan!!! PinmemberJulien Berube11-Jan-07 9:42 
GeneralSQL2005 [modified] Pinmemberjn14821-Nov-06 4:03 
GeneralSaved Again PinmemberJohnDeHope39-Nov-06 3:44 
GeneralMany Thanks Pinmembervirsum22-Mar-05 10:05 
GeneralRe: Many Thanks PinsussMartin Gleeson27-Apr-05 16:08 
GeneralExporting MSSQL users PinsussAnonymous20-Jan-05 6:22 
GeneralWorks Great, but PinmemberT. Bradley Dean27-Jul-04 19:28 
GeneralCool! I'm curious about one thing. PinmemberJeff Varszegi10-Nov-03 7:26 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web02 | 2.8.140721.1 | Last Updated 5 Nov 2003
Article Copyright 2003 by Megan Forbes
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid