CODEWITHSUNDEEP
sqlsql-servert-sql
Robert Kerr
Robert Kerr

Reputation: 1329

Run stored procedure as username determined at deployment time?

Similar to Can you set a stored procedure to always execute as a specific user?

How can you set 'domain/username' to a value determined at deployment time? The username could be different per environment, and I can read a local table to get the service username.

DECLARE @ServiceUsername
SELECT @ServiceUsername = [val] FROM [ConfigTable] WHERE ...
CREATE PROCEDURE dbo.spMyProc
WITH EXECUTE AS @ServiceUsername    /* fails. expects literal string */

Is this possible?

Upvotes: 1

Views: 67

Answers (1)

Jason W
Jason W

Reputation: 13179

It doesn't work exactly like your asking. When you run EXECUTE AS onto a stored procedure, that configuration is part of the stored procedure compilation, so it can't be dynamically tied to a variable.

If you convert your CREATE PROC statements into an NVARCHAR, then you can run the sp_dynamicsql to compile the specific stored proc with the configured username.

Update with sample

Here's a quick work-up I did on my local instance. I ran the code while connected via windows auth, but used dynamic sql to create the proc forcing it to execute as my local SQL account. The proc is simple in that it just outputs the user running the proc to confirm the execution context.

-- Connected with windows auth (domain/jason)
DECLARE @Username VARCHAR(100) = 'jasonw'
DECLARE @SP NVARCHAR(MAX) = '
    CREATE PROC dbo.spMyProc
    WITH EXECUTE AS ''__EXEC_AS_USER__''
    AS
    BEGIN
        PRINT USER_NAME() -- Do work here
    END'
SET @SP = REPLACE(@SP, '__EXEC_AS_USER__', @Username)
EXEC sys.sp_executesql @SP
EXEC dbo.spMyProc

This code correctly outputs jasonw (local SQL account) instead of my windows account.

Upvotes: 2

Related Questions