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
- The
NOT LIKE '%##%'filter excludes names that contain##, such as the internal accounts of SQL Server. The Windows queries do not use it.