SQL server Principal and Mirror logins and permissions Issue

Question

In a mirroring setup where server A is my principal and server B is my mirror, I had some issues related to logins and permissions at the time of fail-over.

During fail-over Server B became my principal and Server A my mirror but I was facing login issues on server B and I had to recreate the logins on server B with the same permissions they had on server A.

What is the correct way to handle this scenario? Do I have to create the logins on server B every time I create a login on server A or is there a better way to handle this?

Are there any scripts available to compare and synchronize logins?

Answer 1

I think you will find the Powershell scripts at dbatools quite useful.

Don't forget - there are other server objects that also do not get mirrored. Sql Agent Jobs and Linked Servers to name a couple.

Answer 2

See the article Transferring Logins to a Database Mirror by Robert Davis, where he writes:

There are two main issues with transferring SQL Logins. Foremost is the SID (security identifier) for the login. The SID must be transferred with the login or it won’t automatically map to the database user. Secondly, the password is not available in clear text, so you can’t just read it from the database.

This script addresses both of these issues. It creates the SQL login with the same SID and it uses the stored hash of the password to recreate the password using the HASHED property to indicate that we are supplying the password already hashed.

There is a link at the bottom to download the script dba_CopyLogins.sql. This will sync your logins between principal and mirror.