sp_helprolemember (Transact-SQL)

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)noAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Returns information about the direct members of a role in the current database.

||
|-|
|Applies to: SQL Server ( SQL Server 2008 through current version).|

Topic link icon Transact-SQL Syntax Conventions

Syntax


sp_helprolemember [ [ @rolename = ] 'role' ]  

Arguments

[ @rolename = ] ' role '
Is the name of a role in the current database. role is sysname, with a default of NULL. role must exist in the current database. If role is not specified, then all roles that contain at least one member from the current database are returned.

Return Code Values

0 (success) or 1 (failure)

Result Sets

Column name Data type Description
DbRole sysname Name of the role in the current database.
MemberName sysname Name of a member of DbRole.
MemberSID varbinary(85) Security identifier of MemberName.

Remarks

If the database contains nested roles, MemberName may be the name of a role. sp_helprolemember does not show membership obtained through nested roles. For example if User1 is a member of Role1, and Role1 is a member of Role2, EXEC sp_helprolemember 'Role2'; will return Role1, but not the members of Role1 (User1 in this example). To return nested memberships, you must execute sp_helprolemember repeatedly for each nested role.

Use sp_helpsrvrolemember to display the members of a fixed server role.

Use IS_ROLEMEMBER (Transact-SQL) to check role membership for a specified user.

Permissions

Requires membership in the public role.

Examples

The following example displays the members of the Sales role.

EXEC sp_helprolemember 'Sales';  

See Also

Security Stored Procedures (Transact-SQL)
sp_addrolemember (Transact-SQL)
sp_droprolemember (Transact-SQL)
sp_helprole (Transact-SQL)
sp_helpsrvrolemember (Transact-SQL)
System Stored Procedures (Transact-SQL)