Share via


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.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/9a385b29-1181-47aa-9f80-062d1bed3cd4/remove-spaces-in-like-search?forum=transactsql

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