Yet another kind of orphan users in SQL Server

Or YAOU for short.

(This silly acronym, I just made it up so don’t try googling it)

The focus of this article is when a use is made owner of the database, without adding it as a user into the database, which, indirectly gives that user database owner permissions:

When you think of an orphan SQL Server user, what comes to your mind? It's a rhetorical question..

Microsoft has started publishing how-to documents on the topics of day-to-day DBA tasks, like how you create a database, how you troubleshoot connections errors etc. These are in addition to the formal product documentation or the tech articles, blogs etc.... Some might say it is an information overload, akin to the concept of Function overloading in programming languages. However, I think it is a very useful service that Microsoft deserves a great credit for.

But, what does that have to do with the topic of orphan users? Because I am now going to refer to one of such how-to document that I came across, Troubleshoot orphaned users. According to it:

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

Well, that kinda jibes with what we DBAs think too. In old days usually the orphan users would be the result of restoring a database from one server to another, even if you create the logins first on the target SQL server, why? Look at the following query to find the orphan 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';

With the advent of the AlwaysOn technology in SQL Server more than 10 years, you may have seen more instances of orphan users. Fortunately, the dbatools have options to help with that too, for example Repair-DbaDbOrphanUser or Copy-DbaLogin to copy login/s from one server to another.

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.

So far, I have not created a login for TestDB01_User01 on the secondary replicas, it is an orphan user there. So, if the AG is failed over to any of the other replicas, obviously TestDB01_User01 will not be able to login there because it doesn’t exist there yet. You can create a login for it either beforehand (preferred) or right after the failover (bad). But if you use the GUI or the same SQL statement to create the login, it will have a different SID and therefore it still won’t be able to login. And the ALTER LOGIN command does not have an option to change a login’s SID, while the CREATE LOGIN command does allow you to assign a particular SID. So, you might think,  you can drop and then recreate the login with a specific SID value. However, in this case you cannot drop a login because TestDB01_User01 is still the database owner, even if just in name.. Sounds confusing, maybe a bug if it is the SID that supposed to drive everything internally? Possibly.

and get your application working again

So, to fix this and bring your users and applications online again, you will need to fail back the AG to another replica, drop the login and recreate it:

 

-- 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 prevent this issue in future, every time you create a new login, you can make sure to create it on all replicas, using the same SID value. Alas… while we may or may not be alone in this universe, more often than not, we are not the only one always responsible for creating new logins.

What can you do then?

You could setup an alert to let you know anytime a new login gets created. That way then you can make sure to add it to all replicas before it becomes a problem. I prefer using the Copy-DbaLogin in PowerShell to accomplish this:

Copy-DbaLogin -Source SQLVM01SQL2016AG01 -Destination SQLVM02SQL2016AG01 -Login 'TestDB01_User01'

 

Type                       Name                      Status

-------------------------    -----------                     -----------------

Login - SqlLogin  TestDB01_User01  Successful

 

A better option would be to use the Sync-DbaAvailabilityGroup as it can take care of more than just logins in AlwaysOn environment. You can schedule it through a SQL agent PowerShell job step to run on a regular basis. And, it has great documentation with examples, or at least better than what I can write up, so please check it out.