Friday, October 31, 2008

EXECUTE AS and REVERT

I learned a new thing today, switching between execution/user contexts using EXECUTE AS and REVERT.
I have been lucky in that I have not encountered too many permission problems due to
  1. I am sysadmin
  2. Most applications run under 'dbo' (probably bad practice)

Books Online has the perfect examples on EXECUTE AS and REVERT

This was my testing code, on testing a Database DDL Trigger


ENABLE
TRIGGER Create_SP_Trigger ON DATABASE
--DISABLE TRIGGER Create_SP_Trigger ON DATABASE
GO

CREATE PROCEDURE Grant_Test_Proc
AS
PRINT
'should have triggered the trigger to auto-grant execute permission to user'
GO

--SELECT SUSER_NAME(), USER_NAME();

EXECUTE AS USER = 'testuser'   -- switches to a limited permission login
EXEC Grant_Test_Proc
GO

--SELECT SUSER_NAME(), USER_NAME();

REVERT;    -- switches to DBO and full permissions
--SELECT SUSER_NAME(), USER_NAME();
DROP PROCEDURE Grant_Test_Proc
GO

No comments:

Post a Comment