Basic Impersonation Using EXECUTE AS
In this post, Data Education founder and trainer Adam Machanic discusses the use of
EXECUTE AS. Here, he explains that this command can be used by any user and that access is controlled by a permissions setting rather than a fixed role.
Switching to a different user’s execution context has long been possible in SQL Server, using the
SETUSER command. This command was only available to members of the
db_owner roles (at the server and database levels, respectively), and was therefore not useful for setting up least-privilege scenarios.
SQL Server 2005 introduced a new command for impersonation,
EXECUTE AS. This command can be used by any user, and access is controlled by a permissions setting rather than a fixed role. The other benefit over
SETUSER is that
EXECUTE AS automatically reverts to the original context at the end of a module.
SETUSER, on the other hand, leaves the impersonated context active when control is returned to the caller. This means that it is impossible to encapsulate impersonation within a stored procedure using
SETUSER and guarantee that the caller will not be able to take control of the impersonated credentials.
To show the effects of
EXECUTE AS, start by creating a new user and a table owned by the user:
CREATE USER Tom WITHOUT LOGIN GO CREATE TABLE TomsData ( AColumn INT ) GO ALTER AUTHORIZATION ON TomsData TO Tom GO
Once the user is created, it can be impersonated using
EXECUTE AS, and the impersonation context can be verified using the
EXECUTE AS USER='Tom' GO SELECT USER_NAME() GO
SELECT statement returns the value Tom, indicating that that is the impersonated user. Any action done after
EXECUTE AS is run will use Tom’s credentials. For example, the user can alter the table, since it owns it. However, an attempt to create a new table will fail, since the user does not have permission to do so:
--This statement will succeed ALTER TABLE TomsData ADD AnotherColumn DATETIME GO --This statement will fail CREATE TABLE MoreData ( YetAnotherColumn INT ) GO
Once you have completed working with the database in the context of Tom’s permissions, you can return to the outer context by using the
REVERT command. If you have impersonated another user inside of another context (i.e., called
EXECUTE AS more than once),
REVERT will have to be called multiple times in order to return context to your login. The
USER_NAME function can be checked at any time to find out whose context you are executing under.
To see the effects of nested impersonation, create a second user. The user can be given the right to impersonate Tom, using
CREATE USER Paul WITHOUT LOGIN GO GRANT IMPERSONATE ON USER::Tom TO PAUL GO
If Paul is impersonated, the session will have no privileges to select rows from the TomsData table. In order to get those permissions, Tom must be impersonated from within Paul’s context:
EXECUTE AS USER='Paul' GO --Fails SELECT * FROM TomsData GO EXECUTE AS USER='Tom' GO --Succeeds SELECT * FROM TomsData GO REVERT GO --Returns 'Paul' -- REVERT must be called again to fully revert SELECT USER_NAME() GO
The most important thing to understand is that when
EXECUTE AS is called, all operations will run as if you are logged in as the impersonated user. You will lose any permissions that the outer user has that the impersonated user does not have, in addition to gaining any permissions that the impersonated user has that the outer user lacks.
For logging purposes, it is sometimes important to record the actual logged in principal. Since both the
USER_NAME function and the
SUSER_NAME function will return the names associated with the impersonated user, the
ORIGINAL_LOGIN function has been added to SQL Server to return the name of the outermost server login. Use of
ORIGINAL_LOGIN will allow you to get the name of the logged-in server principal, no matter how nested your impersonation scope is.