How to distinguish human principals from all the rest in sys.server_principals ?

Question

How to distinguish human principals from all the rest in sys.server_principals ?

SQL Guy 1

SSCoach

Points: 15979
More actions
November 15, 2021 at 3:00 pm

#3953176
Hi all,
If I run this query:
```
select  * 
from    sys.server_principals
where   type = 'U'
        and name not like 'NT%'
```
I get a listing of logins of both types: human which are our team-members, as well as like "...reports", "...developers", etc.
I am tasked to collect a listing of only human members, but there is no visible way from this view to distinguish them. Does anybody know how to do it? Until now I was doing this manually, but now want to create a script for this.
Thanks
November 19, 2021 at 10:36 am

This was removed by the editor as SPAM

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 1

type = 'U' means you are grabbing all "user" type of login. To SQL, a "human" and a "non-human" are identical. It is just a login.

How would SQL know the difference between "reports" and "Steve"?

I can think of a few ways to handle this though. One way you could do this though would be to create a table and insert the names of all non-human accounts and you manually maintain this list as you add more generic accounts. Alternately, store the non-human accounts in an Excel spreadsheet and export the list of names from server principals to that Excel spreadsheet and then filter out the names that match the non-human ones.

But having SQL do it automatically is going to be impossible as SQL doesn't see a difference between a "human" and a "non-human" login.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 2

Mr. Brian Gale wrote:

type = 'U' means you are grabbing all "user" type of login. To SQL, a "human" and a "non-human" are identical. It is just a login.
How would SQL know the difference between "reports" and "Steve"?
I can think of a few ways to handle this though. One way you could do this though would be to create a table and insert the names of all non-human accounts and you manually maintain this list as you add more generic accounts. Alternately, store the non-human accounts in an Excel spreadsheet and export the list of names from server principals to that Excel spreadsheet and then filter out the names that match the non-human ones.
But having SQL do it automatically is going to be impossible as SQL doesn't see a difference between a "human" and a "non-human" login.

The answer there is to have a well established naming convention for non-human logins. For example, all of our service logins start with "svc_" in AD and on SQL Server.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)