Grant set proxy...restrict role to restrict which roles cannot be acquired when switching identities.
The syntax for set proxy is:
grant set proxy to user | role [restrict role role_list | all | system]
where:
role_list – list of roles you are restricting for the target login. The grantee must have all roles on this list, or the set proxy command fails.
all – ensures the grantee can run set proxy only for those users who have the same roles, or a subset of the roles, as the grantee.
system – ensures the grantee has the same set of system roles as the target login.
For example, this grants set proxy to user “joe” but restricts him from switching identities to any user with the sa, sso, or admin roles (however, if he already has these roles, he can set proxy for any user with these roles):
grant set proxy to joe restrict role sa_role, sso_role, admin_role
When “joe” tries to switch his identity to a user with admin_role (in this example, Our_admin_role), the command fails unless he already has admin_role:
set proxy Our_admin_role
Msg 10368, Level 14, State 1: Server 's', Line 2:Set session authorization permission denied because the target login has a role that you do not have and you have been restricted from using.
After “joe” is granted the admin_role and retries the command, it succeeds:
grant role admin_role to joe set proxy Our_admin_role
For more information about the set proxy command, see the Reference Manual: Commands.
