Nirav's Diary

All about Microsoft Technology

Nirav's Diary

Main menu

Skip to primary content
  • Home
  • Nirav Gajjar

Post navigation

← Previous Next →

SQL Server Login Details

Posted on July 13, 2014 by Nirav Gajjar

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
This entry was posted in Uncategorized and tagged get SQL Server Login Details, SQL Server Login Details by Nirav Gajjar. Bookmark the permalink.
Proudly powered by WordPress