Stored Procedures and EXECUTE AS
In this post, Data Education founder and trainer Adam Machanic discusses the use of stored procedures and
EXECUTE AS. Here, he explains that
EXECUTE AS can be used for stored procedures, functions, and triggers.
As described in a previous section in this chapter, the
EXECUTE AS command can be used on its own in T-SQL batches in order to temporarily impersonate other users. However,
EXECUTE AS is also available for stored procedures, functions, and triggers. The examples in this section only focus on stored procedures, but the same principles apply to the other object types.
EXECUTE AS to change the impersonation context of an entire stored procedure, add it to the
CREATE PROCEDURE statement as in the following example:
CREATE PROCEDURE SelectSensitiveData WITH EXECUTE AS 'Louis' AS BEGIN SET NOCOUNT ON SELECT * FROM dbo.SensitiveData END
When this stored procedure is executed by a user, all operations within the procedure will be evaluated as if they are being run by the Louis user rather than by the calling user (as is the default behavior). This includes any dynamic SQL operations, or manipulation of data in tables that the Louis user has access to. When the stored procedure has completed execution, context will be automatically reverted back to that of the caller.
Keep in mind that use of
EXECUTE AS does not break ownership chains, but rather can be used to add to them and create additional flexibility. For instance, consider the following two users and associated tables:
CREATE USER Kevin WITHOUT LOGIN GO CREATE TABLE KevinsData ( SomeData INT ) GO ALTER AUTHORIZATION ON KevinsData TO Kevin GO CREATE USER Hilary WITHOUT LOGIN GO CREATE TABLE HilarysData ( SomeOtherData INT ) GO ALTER AUTHORIZATION ON HilarysData TO Hilary GO
Both users, Kevin and Hilary, own tables. A stored procedure might need to be created that accesses both tables, but using ownership chaining will not work; if the procedure is owned by Kevin, that user would need to be given access to HilarysData in order to select from that table. Likewise for Hilary and the KevinsData table.
One solution in this case is to combine
EXECUTE AS with ownership chaining and create a stored procedure that is owned by one of the users, but executes under the context of the other. The following stored procedure shows how this might look:
CREATE PROCEDURE SelectKevinAndHilarysData WITH EXECUTE AS 'Kevin' AS BEGIN SET NOCOUNT ON SELECT * FROM KevinsData UNION ALL SELECT * FROM HilarysData END GO ALTER AUTHORIZATION ON SelectKevinAndHilarysData TO Hilary GO
Because Hilary owns the stored procedure, ownership chaining will kick in and allow selection of rows from the HilarysData table. But because the stored procedure is executing under the context of the Kevin user, permissions will also cascade for the KevinsData table. In this way, both permission sets can be used, combined within a single module.
Unfortunately, this is about the limit of what can be done using
EXECUTE AS. For more complex permissions scenarios, it is necessary to resort to signing stored procedures using certificates.