WITH CTE_Role (name,role,type_desc)AS(SELECT PRN.name,srvrole.name AS [role] , Prn.Type_Desc FROM sys.server_role_members membership INNER JOIN (SELECT * FROM sys.server_principals WHERE type_desc='SERVER_ROLE') srvrole ON srvrole.Principal_id= membership.Role_principal_id RIGHT JOIN sys.server_principals PRN ON PRN.Principal_id= membership.member_principal_id WHERE Prn.Type_Desc NOT IN ('SERVER_ROLE') AND PRN.is_disabled =0
UNION ALL
SELECT p.[name], 'ControlServer' ,p.type_desc AS loginType FROM sys.server_principals p
JOIN sys.server_permissions Sp
ON p.principal_id = sp.grantee_principal_id WHERE sp.class = 100
AND sp.[type] = 'CL'
AND state = 'G' )SELECT name,Type_Desc ,CASE WHEN [public]=1 THEN 'Y' ELSE 'N' END AS 'Public',CASE WHEN [sysadmin] =1 THEN 'Y' ELSE 'N' END AS 'SysAdmin' ,CASE WHEN [securityadmin] =1 THEN 'Y' ELSE 'N' END AS 'SecurityAdmin',CASE WHEN [serveradmin] =1 THEN 'Y' ELSE 'N' END AS 'ServerAdmin',CASE WHEN [setupadmin] =1 THEN 'Y' ELSE 'N' END AS 'SetupAdmin',CASE WHEN [processadmin] =1 THEN 'Y' ELSE 'N' END AS 'ProcessAdmin',CASE WHEN [diskadmin] =1 THEN 'Y' ELSE 'N' END AS 'DiskAdmin',CASE WHEN [dbcreator] =1 THEN 'Y' ELSE 'N' END AS 'DBCreator',CASE WHEN [bulkadmin] =1 THEN 'Y' ELSE 'N' END AS 'BulkAdmin' ,CASE WHEN [ControlServer] =1 THEN 'Y' ELSE 'N' END AS 'ControlServer' FROM CTE_Role
PIVOT(
COUNT(role) FOR role IN ([public],[sysadmin],[securityadmin],[serveradmin],[setupadmin],[processadmin],[diskadmin],[dbcreator],[bulkadmin],[ControlServer])
) AS pvtWHERE Type_Desc NOT IN ('SERVER_ROLE')ORDER BY name,type_desc
The system stored procedure sp_helprolemember will help us to list the login's database role membership (system defined and user defined database role membership),but this will work only on database level. To get membership across all databases in an instance, this procedure has to run in each databases. The below script will list all users in all databases and its database role membership.
SET NOCOUNT ON
CREATE TABLE #DatabaseRoleMemberShip
(
Username VARCHAR(100),
Rolename VARCHAR(100),
Databasename VARCHAR(100)
)DECLARE @Cmd AS VARCHAR(MAX)DECLARE @PivotColumnHeaders VARCHAR(4000) SET @Cmd = 'USE [?] ;insert into #DatabaseRoleMemberShip
select u.name,r.name,''?'' from sys.database_role_members RM inner join
sys.database_principals U on U.principal_id=RM.member_principal_id
inner join sys.database_principals R on R.principal_id=RM.role_principal_id
where u.type<>''R'''EXEC sp_MSforeachdb @command1=@cmd
SELECT @PivotColumnHeaders =
COALESCE(@PivotColumnHeaders + ',[' + CAST(rolename AS VARCHAR(MAX)) + ']','[' + CAST(rolename AS VARCHAR(MAX))+ ']'
)
FROM (SELECT DISTINCT rolename FROM #DatabaseRoleMemberShip )a ORDER BY rolename ASC
SET @Cmd = 'select
databasename,username,'+@PivotColumnHeaders+'
from
(
select * from #DatabaseRoleMemberShip) as p
pivot
(
count(rolename )
for rolename in ('+@PivotColumnHeaders+') )as pvt'EXECUTE(@Cmd ) DROP TABLE #DatabaseRoleMemberShip
The same scripts are available Server and Database Role Membership.sql
If you liked this post, do like my page on FaceBook
