EXECUTE AS someone else – impersonation in stored procedures
November 11th, 2008
Recently, I had to implement a stored procedure, which is executed under a service account context. It had to be invoked by an Informatica workflow and its purpose was to clean a cache database table and to update all statistics on a database through invoking the sp_updatestats. As the Informatica user did not have enough permissions to perform these tasks, I had to use the EXECUTE AS in the stored procedure. The EXECUTE AS statement can also effectively be used to limit the execution scope through allowing limited permissions to the user account executing the stored procedure:
CREATE PROCEDURE [dbo].[usp_Update_Statistics]WITH EXECUTE AS userASBEGINclear cacheEXEC sp_UpdateStatsEND
Immediately I encountered various problems with the execution of the stored procedure. After some research it turned out that a few preconditions need to be satisfied before we can execute it:
1. TRUSTWORTHY setting on the database needs to be turned on:ALTER DATABASE database SET TRUSTWORTHY ON;2. The owner of both the context database and the master database must be the same. To set this property we need to:2.1 Find who is the owner of master:exec sp_helpdb2.2 Set the context database owner to the same owner:ALTER AUTHORIZATION ON DATABASE::database TO user3. The account under which we EXECUTE AS needs to be a database/server principal. The database and server principals can be found in the sys.database_principals and the sys.server_principals tables.4. The account needs to be granted impersonate permissions:GRANT IMPERSONATE ON USER::user in EXECUTE AS TO user executing the sproc5. If a server login is specified (instead of a database user), it needs to be mapped to a database user.
Also, if SQL Server is running under a local account/service it is not possible to use the EXECUTE AS statement.
The same statement can be used to execute SQL Server Agent jobs under different context – something particularly useful when trying to run them through Integration Services.
If you enjoyed this post, make sure you subscribe to my RSS feed!