Get List of logins on SQL Server instance

-- List of logins

select sp.name as login,

       sp.type_desc as login_type,

       sp.type,

       sp.create_date,

       sp.modify_date,

       case  

           when sp.is_disabled = 1 then 'Disabled'

            else 'Enabled' 

       end as status

from sys.server_principals sp

left join sys.sql_logins sl on sp.principal_id = sl.principal_id

order by sp.name;

 

--Get the list of 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')

and name not like '%##%'

ORDER BY name, type_desc

 

--Get the list of all SQL Login Accounts only

SELECT name, type_desc AS Account_Type

FROM sys.server_principals

WHERE TYPE = 'S'

and name not like '%##%'

 

--Get the list of all Windows Login Accounts only

SELECT name, type_desc AS Account_Type

FROM sys.server_principals

WHERE TYPE = 'U'

 

--Get the list of all Windows Group Login Accounts only

SELECT name, type_desc AS Account_Type

FROM sys.server_principals

WHERE TYPE = 'G'