Tell me more ×
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. It's 100% free, no registration required.
up vote 5 down vote favorite
1

We have 2 servers in an AlwaysOn group.

While the user accounts within each syncronized 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 cooresponding 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

2 Answers

up vote 8 down vote

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 1 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 at 3:30

Your Answer

 
discard

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.