Printed 2015/10/05 06:07PM

SQL Server : List Login's Database and Server Level Role Permission


In my last post, we have discussed about the orphaned users and fixing the orphaned users. The next step in the security audit of our server was to review the login's database role membership and server role membership. 

There is a system stored procedure sp_helpsrvrolemember to list server role membership of login, but it is difficult to review the result of this procedure . Below script will give us the server role membership in more readable format.By copying the the result to Excel, it is more easy to review the server role membership of logins. More over system store procedure will not list the logins which have control server permission.

WITH CTE_Role (name,role,type_desc)
(SELECT, AS [role]
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


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' )

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' 
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.


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,,''?'' 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

@Cmd = 'select
select   * from #DatabaseRoleMemberShip) as p
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

Copyright © 2002-2015 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.