Tuesday , March 19 2024

How To Move Logins To Another Instance(sp_help_revlogin)

 

If you do not use a contained database, you must also move your logins when you move the database to another instance.

You can find detailed information about the Contained Database in the article “What is Contained Database and How To Create“.

You can use the script below to move the logins to another instance.

I use this script. The script create two sp. You will use the first one when you want to transfer logins.

Its called sp_help_revlogin.

And you can find the same script in Microsofts site. I will share it at the end of article.

 

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.

If you do not know about the concept of Stored Procedure, you may want to read the article “Stored Procedure On SQL Server“.

After running the above script on the source server you want to get login information, you are running sp_help_revlogin sp.

This script 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.

When you move the Logins in this way, they are copied along with the SIDs.

That is not the same as creating a new login from scratch on the target with the login name and password in the source.

The SID of a Login is like a fingerprint of login.

The two logins created with the same name and the same password on different instances are different from each other.

For example, you have a login named denemeUser on the Resource Instance.

And let’s assume that this login is db_owner authority in the database named “deneme”.

We transfer the “deneme” database to another instance using the backup restore method.

You can find out more about this process from the article titled “Backup Restore On SQL Server“.

Instead of moving the denemeUser login using the sp_help_revlogin method, we create the same name and the same password on the target instance.

After this process, we will not be able to access the  database named “deneme” with the new login name we created on the target instance.

Normally, when we authorize a login at database level with user mapping in a database, a user belonging to that login under the database is created and this user is moved automatically during backup restore.

However, if you do not move the source login to the target with SID, you will not be able to access the target database.

Because the SIDs of the login that you created with the same name in the target will not match the user being moved to the target with the backup method under the database.

There is another way to equalize the SIDs of logins with the same name. But I do not mention this method because microsoft does not suggest this method.

You can access from the link below.

https://msdn.microsoft.com/en-us/library/ms174378.aspx

Loading

About dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

2 comments

  1. Engr. Mohammad Rizwan Yasin

    This script will not assign permissions?

    how we assign similar permissions for these users between 2 servers?

    • It will assign permissions at database level. Because we are moving logins with their SID. But you should give server level permissions.

Leave a Reply

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

Categories