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.

To use 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.