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.
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
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'
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'
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.