Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Orphaned Users


Orphaned Users

Author
Message
SQLSACT
SQLSACT
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1456 Visits: 2931
Hi All

I'm planning on backing up and restoring +- 20 Databases from one SQL Server to another

I've been reading about orphaned users.

Am I right in the following:

Logins are stored in the master database
DB Users are linked to SQL logins and is stored in the database

My question is: Will I definately end up with orphaned users whenever I restore a database to another server?

If I create the logins on the new server, will SQL make the link automatically between the DB Users and the logins?

Thanks
Suresh B.
Suresh B.
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1128 Visits: 5326
SQLSACT (8/29/2012)
My question is: Will I definately end up with orphaned users whenever I restore a database to another server?

No. Not necessarily. Orphaned users situation araises only if the login does not exist. If the logins exist, no orphansed users.

If I create the logins on the new server, will SQL make the link automatically between the DB Users and the logins?

No/Yes.
Yes, if you create the logins with same SID.
SQLSACT
SQLSACT
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1456 Visits: 2931
Suresh B. (8/29/2012)
SQLSACT (8/29/2012)
My question is: Will I definately end up with orphaned users whenever I restore a database to another server?

No. Not necessarily. Orphaned users situation araises only if the login does not exist. If the logins exist, no orphansed users.

If I create the logins on the new server, will SQL make the link automatically between the DB Users and the logins?

No/Yes.
Yes, if you create the logins with same SID.



Thanks

Yes, if you create the logins with same SID.


How do iIcreate the Logins with the same SID?

Is there a process for this?

Thanks
SQLSACT
SQLSACT
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1456 Visits: 2931
SQLSACT (8/29/2012)
Suresh B. (8/29/2012)
SQLSACT (8/29/2012)
My question is: Will I definately end up with orphaned users whenever I restore a database to another server?

No. Not necessarily. Orphaned users situation araises only if the login does not exist. If the logins exist, no orphansed users.

If I create the logins on the new server, will SQL make the link automatically between the DB Users and the logins?

No/Yes.
Yes, if you create the logins with same SID.



Thanks

Yes, if you create the logins with same SID.


How do I create the Logins with the same SID?

Is there a process for this?

Thanks

Divine Flame
Divine Flame
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1573 Visits: 2801
SQLSACT (8/29/2012)


How do I create the Logins with the same SID?

Is there a process for this?

Thanks


Use sp_help_revlogin stored procedure provided by Microsoft at below link to generate the script of logins with their SID on primary server & execute the same script on secondary server.

http://support.microsoft.com/kb/918992


Sujeet Singh
SQLSACT
SQLSACT
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1456 Visits: 2931
Divine Flame (8/29/2012)
SQLSACT (8/29/2012)


How do I create the Logins with the same SID?

Is there a process for this?

Thanks


Use sp_help_revlogin stored procedure provided by Microsoft at below link to generate the script of logins with their SID on primary server & execute the same script on secondary server.

http://support.microsoft.com/kb/918992


Thanks

I came across this script a while ago, I wasn't sure if it would take the SID as well

Thanks for this
Suresh B.
Suresh B.
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1128 Visits: 5326
SQLSACT (8/29/2012)

How do iIcreate the Logins with the same SID?

Is there a process for this?

You can use SID option in CREATE LOGIN statement (see BOL for more details).

You can get the SID using:
select sid from sys.server_principals where name = 'LoginName'


Arjun SreeVastsva
Arjun SreeVastsva
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2529 Visits: 1642
you can use master.Use sp_help_revlogin stored procedure provided by Microsoft at below link to generate the script of logins with their SID on primary server & execute the same script on secondary server.

http://support.microsoft.com/kb/918992

this will generate a logins scripts.you can take login script frm this.

you can execute login script on target server.

and use exec sp_change_users_login 'update_one','','' to fix
Perry Whittle
Perry Whittle
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8789 Visits: 16560
Suresh B. (8/29/2012)
Orphaned users situation araises only if the login does not exist. If the logins exist, no orphansed users.

This is incorrect!!

The login may exist on the new server but if the SID does not match then you will have an orphaned user. It's the SID mismatch that creates the orphan user scenario

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
Tom Brown
Tom  Brown
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1342 Visits: 1466
Orphaned users are easy to fix:



-- show users not mapped to a login
EXEC sp_change_users_login 'Report'

-- map database user OrphanedUser to Login OrphanedUser
EXEC sp_change_users_login @Action='Update_One', @UserNamePattern='OrphanedUser' , @LoginName='OrphanedUser'



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search