EXECUTE AS

I ran into a situation yesterday where a third party is developing TSQL objects for the rewrite of our external site.  The DBAs were attempting to provide a minimum of rights for that user in the database.  They could create stored procs but couldn't assign permissions to it or even execute themselves.  We have a ticket open with the DBAs for them to correct that permission issue but until that gets queued up, it was looking like I'd be the one granting them permissions each time they recreated the proc.  This vendor loves to play the "I'd have met this milestone, but you didn't provide me with the appropriate resource."  DBAs are slammed so I became the one to grant permissions every time they sent an email and three times was enough for me to automate it.  The easy part was the cursor to iterate through the procs and assign permissions to both the contractor and the SQL Server login that will connect from IIS.  The equally easy part was creating the proc so that it didn't run with the invoker's credentials.  The EXECUTE AS clause saves the day there http://msdn.microsoft.com/en-us/library/ms188354.aspx

 

The code itself is trivial but the idea of writing methods that run under a different context in a SQL Server world merits some investigation.  TSQL below so in case you were curious.

 

CREATE PROCEDURE dbo.PermissionReset
WITH EXECUTE AS owner
AS
BEGIN
    SET NOCOUNT ON

    DECLARE Csr CURSOR
    FOR
    SELECT
        T.name
    FROM
        sys.objects T
    WHERE
        T.type = 'P'

    DECLARE
        @name varchar(500)
    ,   @tsql varchar(max)

    SET NOCOUNT ON
    OPEN Csr
    FETCH NEXT FROM Csr INTO
        @name
    WHILE (@@fetch_status <> -1)
    BEGIN
        IF (@@fetch_status <> -2)
        BEGIN
            SELECT @tsql = 'GRANT EXECUTE ON dbo.' + @name + ' TO [DomainWebUser]'
            EXEC(@tsql)
            SELECT @tsql = 'GRANT EXECUTE ON dbo.' + @name + ' TO [Domain\aUser]'
            EXEC(@tsql)
        END
        FETCH NEXT FROM Csr INTO
        @name
    END
    CLOSE Csr
    DEALLOCATE Csr
END
GO
GRANT EXECUTE ON  dbo.PermissionReset TO [Domain\aUser]
GO