Executing proxy authorization

Follow these rules when you execute set proxy or set session authorization:

You cannot execute set proxy or set session authorization from within a transaction.
You cannot use a locked login for the proxy of another user. For example, if “joseph” is a locked login, the following command is not allowed:
```
set proxy "joseph"
```
You can execute set proxy or set session authorization from any database you are allowed to use. However, the login_name you specify must be a valid user in the database, or the database must have a “guest” user defined for it.
Only one level is permitted; to impersonate more than one user, you must return to your original identity between impersonations.
If you execute set proxy or set session authorization from within a procedure, your original identity is automatically resumed when you exit the procedure.

If you have a login that has been granted permission to use set proxy or set session authorization, you can set proxy to impersonate another user. The following is the syntax, where login_name is the name of a valid login in master..syslogins:

set proxy login_name

set session authorization login_name

Enclose the login name in quotation marks.

For example, to set proxy to “mary,” execute:

set proxy "mary"

After setting proxy, check your login name in the server and your user name in the database. For example, assume that your login is “ralph” and that you have been granted set proxy authorization. You want to execute some commands as “sallyn” and as “rudolph” in pubs2 database. “sallyn” has a valid name (“sally”) in the database, but Ralph and Rudolph do not. However, pubs2 has a “guest” user defined. You can execute:

set proxy "sallyn"
go
use pubs2
go
select suser_name(), user_name()
go

------------------------------ -------------------
sallyn                         sally

To change to Rudolph, you must first change back to your own identity. To do so, execute:

set proxy "ralph"
select suser_name(), user_name()
go

------------------------------ --------------------
ralph                          guest

Notice that Ralph is a “guest” in the database.

Then execute:

set proxy "rudolph"
go
select suser_name(), user_name()
go

------------------------------ --------------------
rudolph                        guest

Rudolph is also a guest in the database because Rudolph is not a valid user in the database.

Now, impersonate the “sa” account. Execute:

set proxy "ralph"
go
set proxy "sa"
go
select suser_name(), user_name()
go

--------------------------- --------------------
sa                          dbo