Click here to Skip to main content
15,867,704 members
Articles / Database Development / SQL Server
Article

SQL Server Permissions - Fixing Orphaned Users

Rate me:
Please Sign up or sign in to vote.
4.41/5 (16 votes)
4 Nov 20032 min read 91.7K   31   9
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


Written By
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 Pin
PGMIKET28-May-07 18:13
PGMIKET28-May-07 18:13 
JokeThanks, Megan!!! Pin
Julien Berube11-Jan-07 9:42
Julien Berube11-Jan-07 9:42 
GeneralSQL2005 [modified] Pin
jn14821-Nov-06 4:03
jn14821-Nov-06 4:03 
THANKS MEGAN!!
Any ideas on how to do this in SQL2005?


-- modified at 10:54 Tuesday 21st November, 2006
Sorry - should have included some notes -
When I attempt this in SQL2005, I get the following:
--- --- ---
Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'sp_change_users_login 'auto_fix', 'NASUser''.
--- --- ---
GeneralSaved Again Pin
JohnDeHope39-Nov-06 3:44
JohnDeHope39-Nov-06 3:44 
GeneralMany Thanks Pin
virsum22-Mar-05 10:05
virsum22-Mar-05 10:05 
GeneralRe: Many Thanks Pin
Martin Gleeson27-Apr-05 16:08
Martin Gleeson27-Apr-05 16:08 
GeneralExporting MSSQL users Pin
Anonymous20-Jan-05 6:22
Anonymous20-Jan-05 6:22 
GeneralWorks Great, but Pin
Member 96099527-Jul-04 19:28
Member 96099527-Jul-04 19:28 
GeneralCool! I'm curious about one thing. Pin
Jeff Varszegi10-Nov-03 7:26
professionalJeff Varszegi10-Nov-03 7:26 

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

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