Orphaned Users

Question

Orphaned Users

SQLSACT

SSC-Insane

Points: 21838
More actions
August 29, 2012 at 12:50 am

#260712

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

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Suresh B. SSC-Insane Points: 22986 More actions · Answer 1

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 SSC-Insane Points: 21838 More actions · Answer 2

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 SSC-Insane Points: 21838 More actions · Answer 3

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 SSCoach Points: 15943 More actions · Answer 4

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 SSC-Insane Points: 21838 More actions · Answer 5

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. SSC-Insane Points: 22986 More actions · Answer 6

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 SSCertifiable Points: 7137 More actions · Answer 7

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 SSC Guru Points: 233821 More actions · Answer 8

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" 😉

Tom257 SSChampion Points: 11543 More actions · Answer 9

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'

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

Tom Brown (9/4/2012)
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'

Just remember, sp_change_users_login:

Maps an existing database user to a SQL Server login. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use ALTER USER instead.

Lowell SSC Guru Points: 323504 More actions · Answer 11

this is a script i whipped together for a different post;

it'ts checking for orphans, and generating the scripts to use if you need them for the fix:

it is using the new ALTER syntax instead.

SELECT

CASE

WHEN svloginz.name is not null and dbloginz.sid <> svloginz.sid

THEN '--Login Exists but wrong sid: remap!

ALTER USER ' + quotename(dbloginz.name) + ' WITH LOGIN = ' + quotename(svloginz.name) + ';'

END

from sys.database_principals dbloginz

LEFT OUTER JOIN sys.server_principals svloginz

on dbloginz.name = svloginz.name

WHERE dbloginz.type IN ('S','U')

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!