:::: MENU ::::

List members of a specific database or server role

  • Jul 30 / 2012
  • 0
dbDigger, Monitoring and Analysis, Security and Permissions, System Stored Procedures

List members of a specific database or server role

SQL Server provides built-in database and server roles. Members of a specific role inherit the privileges of that specific role. Such implicit privileges can not be seen in security related system tables and views. We may find members of a role both by SSMS or T-SQL.

Get members of a role through SSMS

Database roles may be found under security folder of a database. While server roles may be browsed under security folder of server instance.
To view members of a database or server role, just  right click on the role in mentioned folder. There you may get list of role members.
 Get members of a role through SSMS

Get members of a role through T-SQL

More conveniently we may get list of role members both for database role or server role. Following script would list members of sysadmin server role and db_DDLAdmin database role.

 
-- Get members of sysadmin server role   
 EXEC sp_helpsrvrolemember 'sysadmin'   
 GO   
 -- Get members of DDLAdmin database role   
 EXEC sp_helprolemember'db_DDLAdmin'  
 GO  

Role name may be substituted as required in both cases. Result would be 3 column list of role members.

Consult us to explore the Databases. Contact us