SP_HAS_ROLE Function [System]

Returns an integer value indicating whether the invoking user has been granted a specified system privilege or user-defined role. When used for permission checking within user-defined stored procedures, SP_HAS_ROLE returns an error message when a user fails a permission check.

Syntax

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

Arguments

Arguments	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: 1 – display error message specified if system privilege or user-defined role is not granted to invoking user. 0 – (default) do not display error message if specified system privilege or user-defined role is not granted to invoking user.

Result Set

Value Returned	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.

Remarks

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.

If the rolename and grant_type arguments are both NULL and the throw_error argument is 1, you see an error message. You may find this useful for those stored procedures where an error message appears after certain values are read from the catalog tables rather than after the checking the presence of certain system privileges for the invoking user.

Note: A permission denied error message is returned if the arguments rolename and grant_type are set to NULL and throw_error is set to 1, or if all three arguments are set to NULL.

Permissions

None

Example 1

Consider the following scenario:

u1 has been granted the CREATE ANY PROCEDURE system privilege with the WITH NO ADMIN OPTION clause.
u1 has not been granted the CREATE ANY TABLE system privilege.
u1 has been granted the user-defined role Role_A with the WITH ADMIN ONLY OPTION clause.
Role_B exists, but has not been granted to u1
The role Role_C does not exist.

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',1
```
returns 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',1
```
returns 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,1
```
returns the value -1, which indicates the role ROLE_C does not exist.