Sunday, May 10, 2015

Script to get the SQL Logins information


Login type descriptions

S = SQL Server user(SQL_USER)
U = Windows user(WINDOWS_USER)
G = Windows group(WINDOWS_GROUP)
A = Application role(APPLICATION_ROLE)
R = Database role(DATABASE_ROLE)
C = Certificate mapped(CERTIFICATE_MAPPED_USER)
K = Asymmetric key mapped(ASSYMETRIC_KEY_MAPPED_USER)


Using below script you can list all Login Accounts in a SQL Server

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

Using below script you can list all  SQL Login Accounts (Not windows logins)  only in a SQL Server 

SELECT name
FROM sys.server_principals 
WHERE TYPE = 'S'
ORDER BY name, type_desc

Using below script you can list all Windows Login Accounts (Not SQL Logins)  only in a SQL Server 

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

Using below script you can list all Windows Group Login  (Not SQL Logins)  only in a SQL Server 

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