Post reply

Migrating 300+ databases. Need a script to transfer user permissions for all the users across all the databases

  • February 11, 2017 at 6:10 pm

    #320348

    Hello experts,

    I am soon going to be migrating 300+ databases off of are old sql server to the new sql server. I already have the script that will detach, vmware team will copy the files across to the new server and then attach the databases back. However, I need a script that would take care of all the user database permissions across all databases for all logins which would mean all permissions would stay intact post detach. I know someone might have done something similar in the past and we don't want to reivent the wheel. Anyone has any clue on any scripts that you'll use or can point me to the blog or link etc.

    Thanks!

  • February 12, 2017 at 2:33 am

    #1927982

    The users' permissions are stored in the database so the users won't loss there permissions, but you'll might have another problem.  Each user has a unique SID that is mapped to a login that is defined in the master database.  If you create the same user on the new server, the user will have a new SID and the mapping between users and logins won't work.  For windows authentication logins you won't have this problem, but you will have it for SQL Server logins.  You have 2 ways to solve it.  The first one is to create the logins in the master database  with the same SID as they have in the source database (You can use the script from this URL – https://support.microsoft.com/en-us/help/918992/how-to-transfer-logins-and-passwords-between-instances-of-sql-server).  The second way to solve it is to fix the mapping between users and the correct SID with sp_change_users_login stored procedure or with ALTER USER statement.  If the users belong to a server level role, you'll still have to create a script and add them to it.
    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • February 12, 2017 at 3:09 am

    #1927984

    ffarouqi - Saturday, February 11, 2017 6:10 PM

    Hello experts,

    I am soon going to be migrating 300+ databases off of are old sql server to the new sql server. I already have the script that will detach, vmware team will copy the files across to the new server and then attach the databases back. However, I need a script that would take care of all the user database permissions across all databases for all logins which would mean all permissions would stay intact post detach. I know someone might have done something similar in the past and we don't want to reivent the wheel. Anyone has any clue on any scripts that you'll use or can point me to the blog or link etc.

    Thanks!

    You'll find the script you need for this here: 

    Scripting out SQL Server Logins

    😎

  • February 12, 2017 at 2:40 pm

    #1928020

    Eirikur Eiriksson - Sunday, February 12, 2017 3:09 AM

    ffarouqi - Saturday, February 11, 2017 6:10 PM

    Hello experts,

    I am soon going to be migrating 300+ databases off of are old sql server to the new sql server. I already have the script that will detach, vmware team will copy the files across to the new server and then attach the databases back. However, I need a script that would take care of all the user database permissions across all databases for all logins which would mean all permissions would stay intact post detach. I know someone might have done something similar in the past and we don't want to reivent the wheel. Anyone has any clue on any scripts that you'll use or can point me to the blog or link etc.

    Thanks!

    You'll find the script you need for this here: 

    Scripting out SQL Server Logins

    😎

    Eirikur, have you tested the passwords are right?  I remember seeing something like that years ago, but it didn't work.  Granted, it was many years ago and this one looks different, so I'll have play with it.

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

  • February 12, 2017 at 4:31 pm

    #1928024

    Ed Wagner - Sunday, February 12, 2017 2:40 PM

    Eirikur Eiriksson - Sunday, February 12, 2017 3:09 AM

    ffarouqi - Saturday, February 11, 2017 6:10 PM

    Hello experts,

    I am soon going to be migrating 300+ databases off of are old sql server to the new sql server. I already have the script that will detach, vmware team will copy the files across to the new server and then attach the databases back. However, I need a script that would take care of all the user database permissions across all databases for all logins which would mean all permissions would stay intact post detach. I know someone might have done something similar in the past and we don't want to reivent the wheel. Anyone has any clue on any scripts that you'll use or can point me to the blog or link etc.

    Thanks!

    You'll find the script you need for this here: 

    Scripting out SQL Server Logins

    😎

    Eirikur, have you tested the passwords are right?  I remember seeing something like that years ago, but it didn't work.  Granted, it was many years ago and this one looks different, so I'll have play with it.

    I have used this many times, works like a charm.
    😎

  • February 12, 2017 at 6:29 pm

    #1928029

    Eirikur Eiriksson - Sunday, February 12, 2017 4:31 PM

    Ed Wagner - Sunday, February 12, 2017 2:40 PM

    Eirikur Eiriksson - Sunday, February 12, 2017 3:09 AM

    ffarouqi - Saturday, February 11, 2017 6:10 PM

    Hello experts,

    I am soon going to be migrating 300+ databases off of are old sql server to the new sql server. I already have the script that will detach, vmware team will copy the files across to the new server and then attach the databases back. However, I need a script that would take care of all the user database permissions across all databases for all logins which would mean all permissions would stay intact post detach. I know someone might have done something similar in the past and we don't want to reivent the wheel. Anyone has any clue on any scripts that you'll use or can point me to the blog or link etc.

    Thanks!

    You'll find the script you need for this here: 

    Scripting out SQL Server Logins

    😎

    Eirikur, have you tested the passwords are right?  I remember seeing something like that years ago, but it didn't work.  Granted, it was many years ago and this one looks different, so I'll have play with it.

    I have used this many times, works like a charm.
    😎

    I'll second that.  Somewhere along the line, they actually changed it to a stored procedure called "sp_HelpRevLogin".  I've used it many times.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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