has_role

Description

Returns information about whether the user has been granted the specified role.

Syntax

has_role ("role_name", option)

Parameters

role_name

is the name of a system or user-defined role.

option

allows you to limit the scope of the information returned. Currently, the only option supported is 1, which suppresses auditing.

Examples

Example 1

Creates a procedure to check if the user is a System Administrator:

create procedure sa_check as 
if (has_role("sa_role", 0) > 0) 
begin 
    print "You are a System Administrator." 
    return(1) 
end

Example 2

Checks that the user has been granted the System Security Officer role:

select has_role("sso_role", 1)

Example 3

Checks that the user has been granted the Operator role:

select has_role("oper_role", 1)

Usage

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

Any user can execute has_role.

See also

Commands alter role, create role, drop role, grant, set, revoke

Functions mut_excl_roles, role_contain, role_id, role_name, show_role