proc_role

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

proc_role ("role_name")

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

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

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

select proc_role("sso_role")

Checks that the user has been granted the Operator role:

select proc_role("oper_role")

proc_role, a system function, checks whether an invoking user has been granted, and has activated, the specified role.
proc_role returns 0 if any of the following are true:
- the user has not been granted the specified role
- the user has not been granted a role which contains the specified role
- the user has been granted, but has not activated, the specified role
proc_role returns 1 if the invoking user has been granted, and has activated, the specified role.
proc_role returns 2 if the invoking user has a currently active role, which contains the specified role.
For general information about system functions, see “System functions”.

ANSI SQL – Compliance level: Transact-SQL extension.

Any user can execute proc_role.