Nirav's Diary

SQL Server Login Details

By nirav on July 13th, 2014

SQL Server Login Details.

Server Role

SET NOCOUNT ON

;With Cte as
(
SELECT
 @@servername as ServerName,
 p.name AS LoginName,
 CASE WHEN p.is_disabled = 1 THEN 0 ELSE 1 END AS IsEnabled ,
 p.type_desc as LoginType,
 r.type_desc as RoleType,
 r.name as RoleName
FROM sys.server_principals r
INNER JOIN sys.server_role_members m ON r.principal_id = m.role_principal_id
INNER JOIN sys.server_principals p ON p.principal_id = m.member_principal_id
WHERE r.type = 'R'
UNION ALL
select
 @@servername as ServerName,
 R.name AS LoginName,
 CASE WHEN r.is_disabled = 1 THEN 0 ELSE 1 END AS IsEnabled ,
 r.type_desc as LoginType,
 'SERVER_ROLE' as RoleType,
 'public' as RoleName
FROM sys.server_principals r
WHERE r.type IN ('S','U')
and r.name not like '##MS%'
)

--select * from cte

SELECT DISTINCT
r.ServerName,
r.LoginName,
r.IsEnabled,
r.LoginType,
r.RoleType,
STUFF(( SELECT distinct ', '+ a.RoleName
   FROM cte a
   WHERE r.ServerName = a.ServerName
    AND r.LoginName = a.LoginName
    AND r.IsEnabled = a.IsEnabled
    AND r.LoginType = a.LoginType
    AND r.RoleType = a.RoleType
  For XML PATH ('')),1,1,''
 ) as ServerRole
FROM cte r
order by ServerName, LoginType, RoleType, LoginName