User & Login mapping (Backup - restore issue)

Question

Post reply

User & Login mapping (Backup - restore issue)

Hardy21

SSCrazy Eights

Points: 9708
More actions
December 17, 2010 at 12:26 am

#228498

Hello,
I am using SQL Server 2008 R2.
Whenever we are taking backup of the database, all the users, roles & associated schemas are getting backed up while login. So, restoration process will also restore users, roles or schema (if any). But, backup doesn’t have any information of mapping between users & Logins.
I am taking backup and restore to different machine. Is there any way we know that user & login mapping? so I can map the already available users with login, if login is available. If login is not present then I will create login & map to user.
I know SSIS provides facility(task) to transfer logins (also with sid). Also, we can use the scripts to get logins & create the same on destination machine. But, without that is there any way we can find out User & login related mapping?
Regards,
Hardik
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

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

Henrico Bekker One Orange Chip Points: 27652 More actions · Answer 1

You can make use of the SP_help_revlogin as well, works quite well....

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

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 2

Henrico Bekker (12/17/2010)
You can make use of the SP_help_revlogin as well, works quite well....
http://support.microsoft.com/kb/918992

Thanks Henrico. I have already checked this link but this will not work for me. Because we are not able to execute the script for all the servers while doing backup.

Thanks

Wildcat SSCarpal Tunnel Points: 4774 More actions · Answer 3

Hardy21 (12/17/2010)
Henrico Bekker (12/17/2010)
You can make use of the SP_help_revlogin as well, works quite well....
http://support.microsoft.com/kb/918992
Thanks Henrico. I have already checked this link but this will not work for me. Because we are not able to execute the script for all the servers while doing backup.

That script runs against "MASTER" database, and I don't think the backup master db taking much time.

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 4

Wildcat (12/17/2010)
Hardy21 (12/17/2010)
Henrico Bekker (12/17/2010)
You can make use of the SP_help_revlogin as well, works quite well....
http://support.microsoft.com/kb/918992
Thanks Henrico. I have already checked this link but this will not work for me. Because we are not able to execute the script for all the servers while doing backup.
That script runs against "MASTER" database, and I don't think the backup master db taking much time.

It does not take more time but the reason is we are not able to execute the script for each server when we take backup and execute it during database restoration. Thats why I need mapping between users & logins.

Thanks

Wildcat SSCarpal Tunnel Points: 4774 More actions · Answer 5

It does not take more time but the reason is we are not able to execute the script for each server when we take backup and execute it during database restoration. Thats why I need mapping between users & logins.

OK. Here are the steps:

1) Run the sp_help_revlogins against the MASTER on SOURCE SERVER, and get the script of "creating logins".

2) During the restoring the backup to the DESTINATION SERVER, you can run the "creating logins" script against the MASTER database on the DESTINATION SERVER.

3) After the restoration DONE, please run sp_change_users_logins against the RESTORED database.