Situation In SQL Server Availability Groups (AG), Logins must have the same SID (Security identifier) on all the nodes where they use the AG databases. An Availability Group consists of (at least) 2 different SQL Servers: Problem In case of a failover, select/insert/update/delete in that database will fail. Explanation A login (syslogins) has an equivalent record in the database’s sysusers table. The sysusers table holds the security configuration (owner, read/write, execute, etc.) for that login. The connection between those two tables is the SID column. [crayon-5b59c2aa830f8707529550/] Create a login on SQL Server 1 and add that login as a user in an AG database. Then add some rights to that user e.g.: GRANT SELECT ON table TO user. That specific user that is connected via its SID to a specific login with the same SID has been granted those rights. After a failover takes place then that user is no longer connected to the same login because the SID differ. Thus SQL Server is not able after a succesfull login to connect that login to a user in the database and it cannot acquire any rights assigned. The SID will not be the same (1) If you create a login with Management Studio on SQL Server 1 and you do the same on SQL Server 2, you will have 2 different SID’s. The SID will not be the same (2) If you create a login with a Transact-SQL script on both servers, you will have 2 different SID’s. [crayon-5b59c2aa830f8074252760/] Verify if you already have a problem Check 1: Basically the results from both servers need to be the same [crayon-5b59c2aa830f8732906719/] When you execute this on each server in every database that is part of an AG, the results basically should be the same. Basically because 1 server can have more logins then the other if that is how it is configured but the check column should show OK in all cases. If you have orphaned users that are related to the login used for connecting to the AG database, you need to delete the login from the mirror and re-create it with the correct SID. Check 2: Check for orphaned users on the mirror server Orphaned users are users in a database that cannot be linked to a login via their SID. sp_change_users_login helps you out. [crayon-5b59c2aa830f8844965015/] If you have orphaned users that are related to the login used for connecting to the AG database, you need to delete the login and re-create it with the correct SID. How to create a Login in a SQL Server Availability Group Solution Create a login on SQL Server 1 (it does not matter if it is the primary or secondary) Select the SID of the login Create a login on SQL Server 2 using that SID Create a user and give appropriate rights on the primary SQL Server (because the database is in read/write mode) Done [crayon-5b59c2aa830f8933941779/] [crayon-5b59c2aa830f8354717855/] Support In case you are in need of SQL Server support on this subject or others, you can contact us at SQLTreeo via online chat or e-mail servicedesk@sqltreeo.com. We deliver 24x7x365 managed services and support.

The post Create a Login in a SQL Server Availability Group appeared first on SQLTreeo.