Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Orphaned Users Expand / Collapse
Author
Message
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: Friday, July 25, 2014 3:20 AM
Points: 1,375, Visits: 2,661
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
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: Tuesday, July 8, 2014 4:17 AM
Points: 1,101, Visits: 5,279
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
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: Friday, July 25, 2014 3:20 AM
Points: 1,375, Visits: 2,661
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
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: Friday, July 25, 2014 3:20 AM
Points: 1,375, Visits: 2,661
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
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: Today @ 6:17 AM
Points: 1,312, Visits: 2,492
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
Post #1351453
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: Friday, July 25, 2014 3:20 AM
Points: 1,375, Visits: 2,661
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
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: Tuesday, July 8, 2014 4:17 AM
Points: 1,101, Visits: 5,279
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
Posted Thursday, August 30, 2012 12:50 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 8:47 AM
Points: 2,296, Visits: 1,427
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
Posted Tuesday, September 4, 2012 1:54 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:25 PM
Points: 6,194, Visits: 13,350
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
Posted Tuesday, September 4, 2012 2:06 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 1:27 PM
Points: 1,293, Visits: 1,429
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»»

Permissions Expand / Collapse