Returns an integer value indicating whether the invoking user has been granted a specified system privilege or user-defined role. When used for privilege checking within user-defined stored procedures, SP_HAS_ROLE returns an error message when a user fails a privilege check.
dbo.sp_has_role( [rolename], [grant_type], [throw_error] )
Parameters | Description |
---|---|
rolename | The name of a system privilege or user-defined role. |
grant_type | Valid values are: ADMIN and NO ADMIN. If NULL or not specified, NO ADMIN is used by default. |
throw_error | Valid values are:
|
Value | Description |
---|---|
1 | System privilege or user-defined role is granted to invoking user. |
0 or Permission denied: you do not have permission to execute this command/procedure. | System privilege or user-defined role is not granted to invoking user. The error message replaces the value 0 when the throw_error argument is set to 1. |
-1 | The system privilege or user-defined role specified does not exist. No error message appears, even if the throw_error argument is set to 1. |
If the value of the grant_type argument is ADMIN, the function checks whether the invoking user has administrative privileges for the system privilege. If the value of the grant_type argument is NO ADMIN, the function checks whether the invoking user has privileged use of the system privilege or role.
If the grant_type argument is not specified, NO ADMIN is used by default and output indicates only whether the invoking user has been granted, either directly or indirectly, the specified system privilege or user-defined role.
Consider the following scenario:
Based on the above scenario, this command
sp_has_role 'create any procedure'returns the value 1, which indicates u1 has been granted the CREATE ANY PROCEDURE system privilege.
sp_has_role 'create any table'returns the value 0, which indicates u1 has not been granted the CREATE ANY TABLE system privilege. No error message is returned because the throw_error argument is not specified.
sp_has_role 'create any procedure','admin',1returns the Permission denied error message (throw_error=1). Even though u1 has been granted the CREATE ANY PROCEDURE system privilege, u1 has not been granted administrative rights to the system privilege.
sp_has_role 'Role_A'returns the value 1, which indicates u1 has been granted role Role_A.
sp_has_role 'Role_A','admin',1returns the value 1, which indicates u1 has been granted role Role_A with administrative rights.
sp_has_role 'Role_B'returns the value 0, which indicates u1 has not been granted the role ROLE_B. No error message is returned because the throw_error argument is not specified.
sp_has_role 'Role_C'returns the value -1, which indicates the role ROLE_C does not exist.
sp_has_role 'Role_C',NULL,1returns the value -1, which indicates the role ROLE_C does not exist.