You might be wondering, So what's the problem?
In a non-contained database scenario, you would typically first create an instance-level login for this user before you can even make it DB Owner. Otherwise you will get an error message something like "the Login/Principal does not exist...."
But, in an AlwaysOn Availability Group (AG) environment, failing to create a login with the same SID on all secondary replicas can lead to complications. If the database failover to a secondary replica (planned or unplanned), the database user may not have a corresponding instance-level login on that replica. This effectively renders the user an orphan within that context. Interestingly, even though the user now faces access issues, traditional methods for identifying orphan users—whether using classical or more recent SQL queries—might not flag this user as an orphan. This is due to the specific nature of how logins and users are managed and identified in AG environments.
Consider the concept of an orphan SQL Server user. What comes to mind? This is a rhetorical question.
On a related note, Microsoft has recently started publishing comprehensive how-to documents covering day-to-day DBA tasks, such as creating databases and troubleshooting connection errors. These resources serve as a valuable complement to the formal product documentation, technical articles, and blogs. Some may view this abundance of information as overwhelming, reminiscent of the concept of function overloading Function overloading in programming languages. However, I see it as an invaluable service that merits recognition.
So, how does this relate to orphan users? Among these resources, I found a particularly relevant document titled Troubleshoot orphaned users. It sheds light on a scenario where orphaned users in SQL Server arise from a mismatch between database users and their corresponding logins in the master database, specifically when the login no longer exists. According to the document:
Orphaned users in SQL Server occur when a database user is based on a login in the master database, but the login no longer exists in master
In the old days, orphaned users often resulted from restoring a database from one server to another. This could happen even when you preemptively created the logins on the target SQL server. Why? Examine the following query to identify orphaned users:
SELECT dp.type_desc, dp.sid, dp.name AS user_name FROM sys.database_principals AS dp LEFT JOIN sys.server_principals AS sp ON dp.sid = sp.sid WHERE sp.sid IS NULL AND dp.authentication_type_desc = 'INSTANCE';
Ahh… So, it’s not about the user’s name, it’s the SID (Security Identifier) that determines whether a user is orphan. We can use the methods described in Transfer logins and passwords between instances of SQL Server to fix the orphan users. Or, if you like PowerShell as I do, you could also use the dbatools PowerShell toolkit, which I often rely on myself.
But sometimes it’s something else. For example, Orphan Windows Logins, or if the user was granted access to the database in some other way. For example, when a login is not added/granted access to the database, but instead made the database owner without adding it as a user.
This indirectly gives that user database owner permissions. The user in that case is essentially the database owner, without being in the database under its own name and added to the db_owner role. As a result, some of the standard SQL queries to find and fix orphan users won’t detect this.
For the demo, I am going to create a TestDB01 database and add it to an existing AG:
USE [master] GO -- CREATE THE DATABASE CREATE DATABASE [TestDB01]; -- CHANGE THE RECOVERY MODE TO FULL ALTER DATABASE [TestDB01] SET RECOVERY FULL ; -- BACKUP THE DB BACKUP DATABASE [TestDB01] TO DISK = 'TestDB01.BAK'; -- ADD DB TO THE AG ALTER AVAILABILITY GROUP [TestAG] ADD DATABASE [TestDB01]; -- CREATE LOGIN CREATE LOGIN [TestDB01_User01] WITH PASSWORD=N'paue23Y&^97639iqeB', DEFAULT_DATABASE=[TestDB01], CHECK_EXPIRATION=ON, CHECK_POLICY=ON; -- CHANGE THE DATABASE OWNER USE [TestDB01] GO ALTER AUTHORIZATION ON DATABASE::[TestDB01] TO [TestDB01_User01] GO
You won’t find a user with name TestDB01_User01 in the TESTDB01. But check out the SID value for the dbo user and compare it with the SID of the TestDB01_User01 login:
USE [TestDB01] select name, SID, type_desc from sys.database_principals where name = 'dbo' union all select name, SID, type_desc from sys.server_principals where name = 'TestDB01_User01'
The SIDs are the same! Of course, this can happen to any database, not just the ones participating in AG.
Currently, I have not created a login for TestDB01_User01 on the secondary replicas, rendering it an orphaned database user in those instances. If the AG failover to any secondary replica, TestDB01_User01 would be unable to log in due to the absence of a corresponding login. Ideally, creating the login beforehand is recommended to prevent such issues. However, if a login is created post-failover (which is less advised), using either the GUI or the standard SQL statement would most likely result in a different SID, leading to login failure.
To resolve this issue on a new primary replica, the login must be created using T-SQL and the CREATE LOGIN command, which allows for setting a specific SID that aligns with the one already in the database.
-- DROP LOGIN -- DROP LOGIN [TestDB01_User01];
-- RECREATE LOGIN WITH A SPECIFIC SID VALUE CREATE LOGIN [TestDB01_User01] WITH PASSWORD=N'paue23Y&^97639iqeB', DEFAULT_DATABASE=[TestDB01], CHECK_EXPIRATION=ON, CHECK_POLICY=ON, SID = 0xB5AFAA3BF6EA8A489BC5BF6ED35F29B9 ;
To ensure consistent access across replicas in an AlwaysOn Availability Group, it's critical to maintain identical SIDs for logins. If you adjust the database owner's SID on the new primary replica without updating the corresponding logins on other replicas, you risk SID mismatches, complicating future failovers. While it might seem straightforward to drop and recreate the login with the correct SID on any replica, doing so for a login that owns a database requires careful consideration, as this login is integral to the database's access control.
It's important to understand that inconsistencies in SIDs across replicas are not indicative of a bug but are a part of managing database security within SQL Server's framework. To avoid these challenges, proactively ensure that every new login created is replicated across all replicas with matching SIDs. However, in environments with multiple administrators or automated processes, maintaining this consistency requires coordination. DBAs, while central to this process, may need to work collaboratively with other stakeholders to manage logins and user creation effectively.
So, what should you do?
You could setup an alert (through a DDL Trigger, for example), to let you know anytime a new login gets created. This way, you're notified whenever a new login is created. You can then promptly replicate the login across all replicas, ensuring it doesn’t become an issue later on. For this task, I highly recommend utilizing the Copy-DbaLogin command from the PowerShell DBATools toolkit. It's a straightforward and efficient way to ensure your logins are consistently set up across your SQL Server instances:
Copy-DbaLogin -Source SQLVM01\SQL2016AG01 -Destination SQLVM02\SQL2016AG01 -Login 'TestDB01_User01'
Type Name Status
------------------------- ----------- -----------------
Login - SqlLogin TestDB01_User01 Successful
However, the DBATools toolkit has an even more comprehensive solution: the Sync-DbaAvailabilityGroup command. This tool does more than just synchronize logins; it helps maintain uniformity across your AlwaysOn environment on multiple fronts. By scheduling this command to run regularly through a SQL Server Agent job, you can automate the synchronization process, significantly reducing manual maintenance tasks. Plus, dbatools is supported by extensive documentation and user-friendly examples, making it easier than ever to implement these solutions.