Post reply

User & Login mapping (Backup - restore issue)

  • 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

  • December 17, 2010 at 1:52 am

    #1263940

    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

  • December 17, 2010 at 3:33 am

    #1263971

    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

  • December 17, 2010 at 12:29 pm

    #1264204

    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.

  • December 19, 2010 at 11:53 pm

    #1264489

    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

  • December 20, 2010 at 8:16 am

    #1264651

    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.

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

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