Use proc_role within a stored procedure to guarantee that only users with a specific role can execute the procedure. Only proc_role provides a fail-safe way to prevent inappropriate access to a particular stored procedure.
You can use grant execute to grant execute permission on a stored procedure to all users who have been granted a specified role. Similarly, revoke execute removes this permission.
However, grant execute permission does not prevent users who do not have the specified role from being granted execute permission on a stored procedure. If you want to ensure, for example, that all users who are not System Administrators can never be granted permission to execute a stored procedure, use proc_role within the stored procedure itself. It checks to see whether the invoking user has the correct role to execute the procedure.
proc_role takes a string for the required role and returns 1 if the invoker possesses it. Otherwise, it returns 0.
For example, here is a procedure that uses proc_role to see if the user has the sa_role role:
create proc test_proc
as
if (proc_role("sa_role") = 0)
begin
print "You don’t have the right role"
return -1
end
else
print "You have System Administrator role"
return 0
