Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

We have 2 servers in an AlwaysOn group.

While the user accounts within each synchronized database exist on both servers, the database instance level logins only exist on one of the servers. Ie DBINSTANCE->Security->Logins are missing on one server.

Therefore when there is a failover, I get login failures on the second server (which doesn't have the corresponding instance level logins).

How do I overcome this issue? Was I supposed to set up the user account in a special way?

share|improve this question
I've started looking at this one recently too, I rolled my own and am currently describing it in my brand new blog (Mostly for my own benefit as I go forward and forget things but if its of help to others all the better)… this and the following couple will show AlwaysOn failover tracking and job/user synching – user64462 Apr 30 '15 at 13:15
You can use SSMS plugin from sqlskills Synchronize Availability Group Logins and Jobs – Kin Apr 30 '15 at 13:25

My understanding is that if you aren't using Contained Databases, you will have to ensure logins are created on other instances manually.

Something like this script from SQLSoldier, originally posted as Transferring Logins to a Database Mirror, should do the trick.

share|improve this answer
I'm looking forward to converting several of our production '05 databases to the new contained format once we switch to '12 in the next week or two. In the mean time, I'm using Idera's tools to copy logins, jobs, etc, to the secondary instance. – Max Vernon Dec 6 '12 at 2:40
Mark is correct, you'll need to either use contained databases or to manually copy the logins to all the instances in the cluster using the same SID on all the instances. – mrdenny Dec 6 '12 at 12:00
Keep in mind that I am asking about AlwaysOn, not mirroring. I tried the script, however the sys.servers table only contains the local server so I get the following error when I run the stored proceedure: Msg 7202, Level 11, State 2, Line 1 Could not find server 'otherserver' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers. – John Hughes Dec 6 '12 at 14:52
I figured it out and posted a new answer appropriate for AlwaysOn. – John Hughes Dec 6 '12 at 15:46
@JohnHughes This script is appropriate for AlwaysOn. You will however, as indicated by the error message, need to create a linked server between the instances. – Mark Storey-Smith Dec 6 '12 at 15:56
up vote 4 down vote accepted

You must either use a Contained Database, or you must recreate the users on the other server(s) with the same password hash and SID.

A script to do this is provided by Microsoft: How to transfer logins and passwords between instances of SQL Server

Mark's solution was partially right however his recommended solution was for Mirrored databases, as opposed to AlwaysOn which is what the questions asks for.

share|improve this answer
How is the solution for mirrored databases any different from the solution for Availability Groups (note: AlwaysOn is a marketing label for a bunch of technology, not a feature)? The title of a blog post doesn't change the fact that it's restoring missing logins - which, in and of itself, has nothing to do with whether you're using mirroring, availability groups, log shipping, manual backup/restore, etc. – Aaron Bertrand Jun 4 '13 at 3:30

You must use Windows Domain logins and create them in every instance, As the SID is manage by active directory you will be able to access in all replicas members of availability group if the login exist in the primary replica. another option you must use a certificate.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.