System security officers use the grant set proxy or grant set session authorization command to give a user permission to impersonate another user within the server. The user with this permission can then execute either set proxy or set session authorization to become another user.
To grant proxy authorization permission, you must be a system security officer and execute the grant command from the master database. The syntax is:
grant set proxy to user | role [restricted role role_list | all | system]
where:
role_list – list of roles you are restricting for the target login. If the grantees do not yet have the roles in the role_list granted to them, set proxy to the target login fails if the target login contains roles in the role_list granted.
all – when used to grant set proxy to role_list, restricts granting the grantee any new roles when switching identities.
system – ensures the grantee has the same set of system roles as the target login.
Example 1: This example grants set proxy to user “joe” but restricts him from switching identities to any user with the sa_role, sso_role, or admin_role roles (however, if he already has these roles, he can set proxy for any user with these roles):
grant set proxy to joe restricted 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
Example 2: Restricts “joe” from being granted any new roles when switching identities:
grant set proxy to joe restricted role all
“joe” can grant set proxy only to users who have the same (or a subset of) roles that he has.
Example 3: Restricts Joe from acquiring any new system roles when using set proxy:
grant set proxy to joe restricted role system
set proxy fails if the target login has system roles that joe lacks.
