All Logins in sql server

–1–Get the list of all Logins in SQL Server

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

–2–List of all SQL Logins only

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

–3–Get the list of all Logins in SQL Server

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

–4–List of all Windows Group Login Accounts only

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

–5–List any specific windows principals or active directory

SELECT * FROM syslogins where loginname like 'Domain1\User1'

–6–To check the default db of all logins

SELECT name as Login_Name, type_desc as Account_type, 
default_database_name defaultdb 
FROM sys.server_principals;
Unknown's avatar

Published by Manish Kaushik

Leave a comment