How do I tell if a user is orphaned or actually created without a login?

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 authentication
• INSTANCE : Instance authentication
• DATABASE : Database authentication
• WINDOWS : Windows authentication
• EXTERNAL: 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 .