Sunday, 5 August 2012

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


  1. The second SQL is neat. However, it shows dbroles for disabled logins as well. Can we elimiate disabled users?

  2. Nice CTE! However ... everyone is member of the PUBLIC role and membership cannot be revoked:

    Msg 15081, Level 16, State 1, Procedure sp_dropsrvrolemember, Line 34 - Membership of the public role cannot be changed.

    You might consider removing the PUBIC role from your query. It is incorrectly never reported on, which is detrimental to the reliability of the outcome of your query. Commenting out the first CASE statement will suffice.

  3. Thanks for sharing this valuable information about sql server database.

  4. || || || || || || || || || || || || || || || || || || || || || || || || |||||||| || || || |||||||||||||||||||||||||||||||||||||||||| || || || || || || || || || || || || |||||||| || || || |||||||||||||||||||||||||||||||||||||||||| ||

  5. Thanks for information Export Movers and Packers services in India provider low price save your time, comes with moving and packing services.
    Movers and Packers Mumbai
    Movers and Packers Hyderabad
    Movers and Packers Bangalore
    Movers and Packers Chennai
    Movers and Packers Pune

  6. How did you can avail the give your dream a shape with the help of make your move Adword India's No 1 online business experts.

  7. If you are Packers and Movers gives you stress free relocation services provide transport and moving services
    Packers and Movers Delhi
    Packers and Movers Noida
    Packers and Movers Faridabad
    Packers and Movers Ghaziabad

  8. All Packers and Movers India relocation services provided this company services are available pricrs for Top3th.
    Packers and Movers in Gurgaon
    Packers and Movers in Thane
    Packers and Movers in Navi Mumbai