How do I tell if a user is orphaned or actually created without a login?
5July 18, 2023 by Kenneth Fisher
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 🙃.
[…] Kenneth Fisher needs more factory workers: […]
Thanks for the concept and code. My problem is generally the opposite. We lose a customer, and (eventually) drop the database. We often don’t get the Login dropped at the same time. So I need to reverse this and look for Logins not associated with any User or DB.
Yea, I’ve done that. Lots of not exists 😁
I love these kinds of useful tidbits, especially since we’re doing migrations right now.
Glad to hear it! I’ve built it into sp_dbpermissions I just haven’t posted about it yet.