LLI Wiki — My Digital Knowledge Base

List server logins by type

List SQL logins, Windows logins and Windows groups of a SQL Server instance from sys.server_principals.

Server-level logins are stored in sys.server_principals. Its type column tells the kind of account: S for a SQL Server login, U for a Windows login and G for a Windows group.

All logins with their type

SELECT name AS Login_Name, type_desc AS Account_Type
FROM sys.server_principals 
WHERE TYPE IN ('U', 'S', 'G')
and name not like '%##%'
ORDER BY name, type_desc

SQL logins only

SELECT name
FROM sys.server_principals 
WHERE TYPE = 'S'
and name not like '%##%'

Windows logins only

SELECT name
FROM sys.server_principals 
WHERE TYPE = 'U'

Windows groups only

SELECT name
FROM sys.server_principals 
WHERE TYPE = 'G'

Notes

Mastodon LLIMastodon SocialFosstodon