Click here to Skip to main content
12,403,948 members (69,387 online)
Click here to Skip to main content
Add your own
alternative version

Stats

82.1K views
31 bookmarked
Posted

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

Share

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[^]

You may also be interested in...

Comments and Discussions

 
GeneralBrilliant, cheers Pin
PGMIKET28-May-07 18:13
memberPGMIKET28-May-07 18:13 
JokeThanks, Megan!!! Pin
Julien Berube11-Jan-07 9:42
memberJulien Berube11-Jan-07 9:42 
GeneralSQL2005 [modified] Pin
jn14821-Nov-06 4:03
memberjn14821-Nov-06 4:03 
GeneralSaved Again Pin
JohnDeHope39-Nov-06 3:44
memberJohnDeHope39-Nov-06 3:44 
GeneralMany Thanks Pin
virsum22-Mar-05 10:05
membervirsum22-Mar-05 10:05 
GeneralRe: Many Thanks Pin
Martin Gleeson27-Apr-05 16:08
sussMartin Gleeson27-Apr-05 16:08 
GeneralExporting MSSQL users Pin
Anonymous20-Jan-05 6:22
sussAnonymous20-Jan-05 6:22 
GeneralWorks Great, but Pin
T. Bradley Dean27-Jul-04 19:28
memberT. Bradley Dean27-Jul-04 19:28 
GeneralCool! I'm curious about one thing. Pin
Jeff Varszegi10-Nov-03 7:26
memberJeff 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.

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