One of the wonderful things about blogging is the ability to make notes for future me. Which is basically what this is going to be. This is a pretty niche piece of information so unless you are a security nut like me (Hi Sean! ) you may not be interested in this. Either way, here we go.
When you create a user (a database principal) you have several options on what the user is associated with (usually a login/server principal), or it might not be associated with anything at all (created without a login). And a common problem is when that object youβve associated your user with is no longer available and youβve got an orphan.
You can tell if a user might be an orphan easily enough.
SELECT * FROM sys.database_principals
WHERE sid NOT IN (SELECT sid FROM sys.server_principals)
AND TYPE NOT IN ('A','R') ;
Roles and Application roles canβt be orphans so there is no point in checking them.
When I first started writing this post (almost a year ago) I had found a really interesting blog with a piece of code that used some calculations on the SID to tell if the database principal (user) should or should not have a server principal (login) associated with it. I canβt find it anymore . Thatβs what I get for not taking better notes. Fortunately, I found a better way.
SELECT name, type, authentication_type_desc FROM sys.database_principals;
There is a handy dandy column in sys.database_principals called authentication_type_desc. Per the documentation this is whatβs in there:
NONE
Β : No authenticationINSTANCE
Β : Instance authenticationDATABASE
Β : Database authenticationWINDOWS
Β : Windows authenticationEXTERNAL
: Azure Active Directory authentication
It tells you where the id is going to be authenticated from. In very simplified terms where is the password. Windows: Active Directory/Windows, External: Azure Active Directory, Instance, Database: SQL Auth and None: THERE IS NO Authentication! So Roles, SQL Ids created without a login, etc. So simple query to get orphans:
SELECT dp.name
FROM sys.database_principals dp
WHERE authentication_type_desc <> 'NONE'
AND NOT EXISTS (SELECT 1 FROM sys.server_principals sp
WHERE dp.sid = sp.sid);
I should point out Iβm not taking into account partially contained databases and the possibility of having a user completely contained by the database. That might be the Database authenticated but I havenβt had time to do any testing on that yet. Iβll let you know .