Creating a Login

06 Jun 2022 51721 views 0 minutes to read Contributors

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 if the login SID's are not equal on both SQL's

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.

		
			
T-SQL
			
Transact-SQL
			

			
USE [database]
GO
SELECT su.sid, su.name, sl.name, CASE WHEN sl.name IS NULL THEN 'SID is not equal' ELSE 'Ok' END as Check FROM dbo.sysusers su LEFT OUTER JOIN dbo.syslogins sl on su.sid=sl.sid where issqluser = 1 and su.name not in ('guest', 'INFORMATION_SCHEMA', 'sys')
			

				

					

				

					
1
2
3
				
						
USE [database]
GO
SELECT su.sid, su.name, sl.name, CASE WHEN sl.name IS NULL THEN 'SID is not equal' ELSE 'Ok' END as Check FROM dbo.sysusers su LEFT OUTER JOIN dbo.syslogins sl on su.sid=sl.sid where issqluser = 1 and su.name not in ('guest', 'INFORMATION_SCHEMA', 'sys')
					
				
			
		

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.

		
			
T-SQL
			
Transact-SQL
			

			
USE [master]
GO
CREATE LOGIN [test] WITH PASSWORD=N'test', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
			

				

					

				

					
1
2
3
4
				
						
USE [master]
GO
CREATE LOGIN [test] WITH PASSWORD=N'test', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
					
				
			
		

Verify if you already have a problem

Check 1:

Basically the results from both servers need to be the same

		
			
T-SQL
			
Transact-SQL
			

			
USE [database]
GO
SELECT su.sid, su.name, sl.name, CASE WHEN sl.name IS NULL THEN 'SID is not equal' ELSE 'Ok' END as Check FROM dbo.sysusers su LEFT OUTER JOIN dbo.syslogins sl on su.sid=sl.sid where issqluser = 1 and su.name not in ('guest', 'INFORMATION_SCHEMA', 'sys')
			

				

					

				

					
1
2
3
				
						
USE [database]
GO
SELECT su.sid, su.name, sl.name, CASE WHEN sl.name IS NULL THEN 'SID is not equal' ELSE 'Ok' END as Check FROM dbo.sysusers su LEFT OUTER JOIN dbo.syslogins sl on su.sid=sl.sid where issqluser = 1 and su.name not in ('guest', 'INFORMATION_SCHEMA', 'sys')
					
				
			
		

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.

		
			
T-SQL
			
Transact-SQL
			

			
USE [database]
GO
-- A Microsoft SQL Server standard system procedure
EXEC sp_change_users_login 'report'
			

				

					

				

					
1
2
3
4
				
						
USE [database]
GO
-- A Microsoft SQL Server standard system procedure
EXEC sp_change_users_login 'report'
					
				
			
		

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

1. Create a login on SQL Server 1 (it does not matter if it is the primary or secondary)
2. Select the SID of the login
3. Create a login on SQL Server 2 using that SID
4. Create a user and give appropriate rights on the primary SQL Server (because the database is in read/write mode)
5. Done

		
			
T-SQL
			
Transact-SQL
			

			
-- Execute on the primary or secondary server
USE [master]
GO
CREATE LOGIN test WITH PASSWORD = 'TesT123', CHECK_POLICY=OFF

-- Select the SID
SELECT sid, name FROM syslogins where name = 'test'
			

				

					

				

					
1
2
3
4
5
6
7
				
						
-- Execute on the primary or secondary server
USE [master]
GO
CREATE LOGIN test WITH PASSWORD = 'TesT123', CHECK_POLICY=OFF
 
-- Select the SID
SELECT sid, name FROM syslogins where name = 'test'
					
				
			
		

		
			
T-SQL
			
Transact-SQL
			

			
-- Execute on the SQL Server(s) where you want to create the login
USE [master]
GO
CREATE LOGIN test WITH  PASSWORD=N'TesT123', sid = 0xF0462AD0BEAC7C46B40D1D5D79C32386, CHECK_POLICY=OFF
			

				

					

				

					
1
2
3
4
				
						
-- Execute on the SQL Server(s) where you want to create the login
USE [master]
GO
CREATE LOGIN test WITH  PASSWORD=N'TesT123', sid = 0xF0462AD0BEAC7C46B40D1D5D79C32386, CHECK_POLICY=OFF
					
				
			
		

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.