I am new to Always On Availability Groups and I am little bit confused with creation of logins.

Let's assume an environment NODE1(Primary) & NODE2(secondary) & NODE3(Stand by) & VAEWI(Listener).

  1. I need to create a SQL authentication login.

    For this, I assume I need to create it by connecting to listener that which will sync in all of the nodes.

  2. I need to create a Windows authentication.

    For this I think, as all of the nodes are window-clustered before creating the availability group. So, we need to create a Windows user 1st in primary node and then create a Windows authentication by connecting to the listener.

Can anyone let me know if my assumptions are true?


Putting in a simple language :

A login gives you access to server.

A user gives you access to database.

A login must map a user so that you can access the database.

For SQL Authentication, you should create the login with SAME SID and map it to your user database/s on all the nodes with proper permissions (follow the principle of least privileged). This script will help you or this SSMS addin from sqlskills.

For Windows Authentication, you should create a login and map it to your user database. Create/transfer the same login to other nodes.

A listener is a virtual interface and has nothing to do with logins or users.

  • Can I just create a login and then mapped it to a user /database and do the same thing on the secondary ? – SqlNovice Dec 19 '17 at 16:57
  • On Secondary, just create login with same sid. The db permissions are inherited. – Kin Shah Dec 19 '17 at 16:59
  • I guess we need this for sql logins only not windows authenticated logins – SqlNovice Dec 19 '17 at 17:01
  • Yes.. for windows login, you dont have to use sid, since sid for windows login never change. For sql logins, you have to use same sid across all nodes. – Kin Shah Dec 19 '17 at 17:21

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.