I have been lucky in that I have not encountered too many permission problems due to
- I am sysadmin
- 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
