How To Migrate MSSQL Server Logins To Another Instance

 

Step 1: Create SP sp_help_revlogin on master database of Source / Primary instance

The following script create two stored-procedures. You will use the first one when you want to transfer logins.

https://support.microsoft.com/en-us/kb/246133

This script creates two stored procedures in your master database named sp_help_revlogin and sp_hexadecimal.

 

Step 2: Execute the SP sp_help_revlogin on Source Instance

This gives you the scripts of the Login’s you want to carry in encrypted form. You can run it on the target server by copying the part of the login you want to move from the script.

 

Step 3: Drop Role Members & Users on target machine

Execute the following 2 queries on target machine to extract the members of roles and users in the current database

3a) Query1

 

3b) Query2

 

3c) Drop Role Members & Users

Extract the output of the above 2 queries and execute on the same target machine to drop role members and users from the database accordingly.

 

Step 4: Add Users &  Role Members on target machine

Execute the following 2 queries on source/production instance to extract the members of roles and users in the current database

4a) Query1

 

4b) Query2

 

4c) Add Role Members & Users

Extract the output of the above 2 queries and execute on the same target machine to drop role members and users from the database accordingly.

 

Step 5: Grant Permission to Users for specific Objects

Execute the following script on source / production server to extract the permissions on objects.

 

Export the output generated from this query and execute it on the target server to assign the relevant permissions.

CHECK & Verify Login Access and the permissions of database objects and their specific roles respectively.

 

Leave a Reply

Your email address will not be published. Required fields are marked *