Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2008 » Security (SS2K8) » Orphaned Users
Add to briefcase
12 posts, Page 1 of 2
12»»

Orphaned Users Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
SQLSACT
Posted Wednesday, August 29, 2012 12:50 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 8:44 AM
Points: 1,189, Visits: 2,107
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
Post #1351421
Suresh B.
Posted Wednesday, August 29, 2012 1:05 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 2:38 AM
Points: 1,075, Visits: 5,116
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.
Post #1351430
SQLSACT
Posted Wednesday, August 29, 2012 1:55 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 8:44 AM
Points: 1,189, Visits: 2,107
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
Post #1351450
SQLSACT
Posted Wednesday, August 29, 2012 1:56 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 8:44 AM
Points: 1,189, Visits: 2,107
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
Post #1351451
Divine Flame
Posted Wednesday, August 29, 2012 2:01 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, May 17, 2013 2:00 PM
Points: 1,076, Visits: 1,914
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

- Divine Flame
Post #1351453
SQLSACT
Posted Wednesday, August 29, 2012 2:08 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 8:44 AM
Points: 1,189, Visits: 2,107
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
Post #1351457
Suresh B.
Posted Wednesday, August 29, 2012 2:51 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 2:38 AM
Points: 1,075, Visits: 5,116
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'
Post #1351476
Sree Arjun Div
Posted Thursday, August 30, 2012 12:50 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 3:44 AM
Points: 2,173, Visits: 1,350
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
Post #1352027
Perry Whittle
Posted Tuesday, September 04, 2012 1:54 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:36 AM
Points: 5,201, Visits: 11,153
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"
Post #1354159
Tom Brown
Posted Tuesday, September 04, 2012 2:06 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 5:16 AM
Points: 1,196, Visits: 1,319
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'
Post #1354164
« Prev Topic | Next Topic »

Add to briefcase
12 posts, Page 1 of 2
12»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.