Trigger

Earlier this week I had to disable many triggers just so that we can Restore Databases and Create Databases

The triggers were set to send out alerts on DDL events but 2 issues

1. The DBA db didn't exist on that server but the trigger is relying on that db, so I found the triggers using the below scripts, and disabled them (or a simple SELECT * FROM sys.triggers would work too)

SELECT
             sysobjects.name AS [Trigger Name],
             syscomments.TEXT AS [Trigger Definition],
             --SUBSTRING(syscomments.text, 0, 26) AS [Trigger Definition],
             OBJECT_NAME(sysobjects.parent_obj) AS [Table Name],
             syscomments.encrypted AS [IsEncrpted]
FROM
             sysobjects INNER JOIN syscomments ON sysobjects.id = syscomments.id
WHERE
             (sysobjects.xtype = 'TR')
ORDER BY [Table Name]


2. SharePoint creating database name so long that it broke the XML column used in the DDL trigger for Creating Database, so I disabled the DDL Trigger by

1. Find the trigger name (KEY!)
Server -> Server Objects -> Triggers -> trigger_name

2. DISABLE TRIGGER trigger_name

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