能否在存储过程中执行sp_setapprole？求可行方案或变通方法

Great question! Let’s tackle this head-on because while sp_setapprole can be called inside a stored procedure, there’s a critical gotcha that makes it feel unworkable if you don’t handle it right.

The core issue: When you run sp_setapprole, it switches the current connection’s security context to the application role—and this switch doesn’t automatically revert when the stored procedure finishes. That means subsequent operations on the same connection will run with the app role’s permissions, which is almost never what you intend.

Here are the most reliable workarounds to fix this:

1. Use sp_setapprole with a Cookie + Safe Context Reversion #

This is the most direct fix for the context problem. When you call sp_setapprole, you can generate a "cookie" that lets you explicitly revert back to the original security context when you’re done. Always wrap this in error handling to ensure reversion even if something goes wrong.

Example code:

CREATE PROCEDURE dbo.RunWithAppRole
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @AppRoleCookie VARBINARY(8000);

    BEGIN TRY
        -- Activate the app role and generate a cookie for reversion
        EXEC sp_setapprole
            @rolename = 'YourApplicationRoleName',
            @password = N'YourSecureAppRolePassword', -- Store this securely, don't hardcode!
            @fCreateCookie = 1,
            @cookie = @AppRoleCookie OUTPUT;

        -- Perform your restricted operations here
        INSERT INTO dbo.SensitiveData (RecordValue) VALUES ('Protected entry');
        UPDATE dbo.RestrictedSettings SET IsEnabled = 1 WHERE SettingID = 5;
    END TRY
    BEGIN CATCH
        -- Re-throw the error after handling
        THROW;
    END CATCH
    FINALLY
        -- Always revert the context, even if an error occurred
        IF @AppRoleCookie IS NOT NULL
            EXEC sp_unsetapprole @AppRoleCookie;
    END FINALLY;
END;

Important note: Never hardcode the app role password. Use SQL Server’s credential store or encryption (like ENCRYPTBYKEY) to keep it secure.

2. Replace with EXECUTE AS (If Permissions Allow) #

If your use case doesn’t strictly require an application role, consider using EXECUTE AS to switch to a dedicated privileged user instead. This lets you easily revert context without needing a cookie.

Example:

CREATE PROCEDURE dbo.RunWithElevatedPermissions
AS
BEGIN
    SET NOCOUNT ON;

    -- Switch to a user with the required permissions
    EXECUTE AS USER = 'PrivilegedOperationUser';

    BEGIN TRY
        -- Perform restricted operations
        DELETE FROM dbo.ExpiredRecords WHERE ExpiryDate < GETDATE();
    END TRY
    BEGIN CATCH
        THROW;
    END CATCH
    FINALLY
        -- Revert back to the original caller's context
        REVERT;
    END FINALLY;
END;

This works best if you can create a user with exactly the permissions you need (instead of using an app role). It’s simpler and avoids some of the app role’s edge cases.

3. Use a Separate Connection (For Advanced Scenarios) #

If you need to isolate the app role context completely from the original connection, you can spin up a separate connection within the stored procedure (using OPENROWSET or a CLR stored procedure). This way, the original connection’s context never changes.

Example with OPENROWSET (requires enabling Ad Hoc Distributed Queries):

CREATE PROCEDURE dbo.RunAppRoleInIsolation
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @AppRolePassword NVARCHAR(128) = (SELECT CONVERT(NVARCHAR(128), DECRYPTBYKEY(CredentialBlob)) FROM dbo.SecureCredentials WHERE CredentialName = 'AppRolePassword');
    DECLARE @SqlScript NVARCHAR(MAX);

    SET @SqlScript = N'
        EXEC sp_setapprole ''YourApplicationRoleName'', ''' + @AppRolePassword + ''';
        -- Run your app role-only operations here
        SELECT * FROM dbo.TopSecretData;
    ';

    -- Execute in a separate connection
    SELECT * INTO #TempResults
    FROM OPENROWSET('SQLNCLI', 'Server=(local);Trusted_Connection=yes;', @SqlScript);

    -- Use the temp results as needed
    SELECT * FROM #TempResults;
    DROP TABLE #TempResults;
END;

Note: Enabling Ad Hoc Distributed Queries has security implications, so only use this if the other methods don’t fit your use case.

To recap: You can use sp_setapprole in a stored procedure, but you must handle context reversion explicitly. The cookie method is the most straightforward and safe approach for most scenarios.

内容的提问来源于stack exchange，提问作者Thomas5897