0

What is the proper way to create logins on Always on? An application database requires an active directory login and I created that on the primary node. The database is still not in the availability group yet. I plan to move it there once the application is installed. Do I need to create the same login on the secondary node as well ? Or would the login sync across the secondary node once the database is moved into the AG? The only permission which was requested was db_owner . Are there any other permissions which I need to give to the login to properly work within the availability group ? Please let me know. Thanks !

3

Logins and users work the same way they do with DBM or log shipping. If using SQL Server authentication, you have to sync the SIDs manually (or manually create the login with the right SID) assuming the login is already created on the other instance.

Either way, this is not automatically done. You need to create the login at the instance level to match the DB user. If using Windows authentication, it should have the same SID, but you still need to create the login at the instance level.

Remember, the instance-level login sits outside the AG, so the AG won't do anything there. Not sure why you thought the AG would work this way; it never has, just like DBM and log shipping.

Also, there is no feature called Always On. Please do not refer to AGs that way. http://sqlha.com/2013/04/29/alwayson-is-the-new-activepassive-and-activeactive/

  • There is nothing here that Alan and I didn't already answer on Twitter when you asked there. – Robert L Davis Dec 19 '16 at 4:47
  • Thank you I will be referring to it as AG from now . And yes because it's an active directory account the SID would be similar. The only clarity which I required was whether to create the login on both the nodes. – user2923332 Dec 19 '16 at 6:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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