Executing proxy authorization

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

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

or

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