Auditing the usage of “EXECUTE AS” in a database
A question was recently asked in the MSDN Transact-SQL Forum about detecting the usage of “EXECUTE AS” in a specific database.
The “EXECUTE AS” clause was introduced in SQL Server 2005 as a powerful execution context enhancement to grant permission to a function, stored procedure, or trigger, while maintaining strict control over permissions on the underlying objects. For example, let’s say that a stored procedure retrieves data from five different tables, each with different security permissions. By granting “EXECUTE AS” permission to a specific user/login/context, the stored procedure can be executed without modifying permissions to the underlying tables. While this may be convenient and well designed, it may also be a result of someone being too lazy to understand the security model of their database. Querying the built-in system tables for a list of database objects that have defined an “EXECUTE AS” context is a good exercise for any DBA. Here is a small query to list the object name, type, and execution context that was supplied via “EXECUTE AS”:
SELECT sp.name AS [Name], sp.type_desc,
ISNULL(USER_NAME(smsp.execute_as_principal_id),'') AS [ExecutionContextPrincipal]
FROM sys.all_objects AS sp
LEFT OUTER JOIN sys.sql_modules AS smsp ON smsp.object_id = sp.object_id
WHERE smsp.execute_as_principal_id IS NOT NULL