sp_has_role system procedure

sp_has_role( 
rolename 
[, grant_type
[, throw_error ] ]
)

rolename Use this CHAR(128) parameter to specify the name of a system privilege or user-defined role to check for.
grant_type Use this optional CHAR(20) parameter to specify the grant type to check for. Possible values are: ADMIN, or NO ADMIN (the default).
If set to ADMIN, the sp_has_role procedure checks whether the invoking user has administrative rights for the privilege or role. If set to NO ADMIN, sp_has_role checks whether the invoking user has the rights to exercise the privilege or role.
throw_error Use this optional INTEGER parameter to specify whether to return a value indicating the outcome of the privilege check. If 1 is specified, a message is returned if the invoker has not been granted the specified privilege or role. If 0 is specified (the default), no message is returned, regardless.

Value returned	Description
1	The system privilege or user-defined role is granted to invoking user, and the grant_type check passes as well.
0 or Permission denied: you do not have permission to execute this command/procedure.	The system privilege or user-defined role is not granted to invoking user, and/or the grant_type check failed as well. The error message replaces the value 0 when throw_error is set to 1.
-1	The system privilege or user-defined role specified does not exist. No error message appears, even if throw_error is set to 1.

The throw_error argument is useful for returning "permission denied" error messages when a user fails the permission check in a stored procedure.

None

The following statement checks whether you have been granted the CREATE ANY PROCEDURE system privilege with no administrative rights.
SELECT sp_has_role( 'CREATE ANY PROCEDURE' );
The following statement checks whether you have been granted the CREATE ANY PROCEDURE system privilege with administrative rights and returns an error only if you have not been granted it.
SELECT sp_has_role( 'CREATE ANY PROCEDURE', 'ADMIN', 1 );
The following statement checks whether you have been granted Role_A with no administrative rights.
SELECT sp_has_role( 'Role_A' );
The following statement checks whether you have been granted Role_A with administrative rights and returns an error only if you have not been granted it.
SELECT sp_has_role( 'Role_A', 'ADMIN', 1 );