Archive

Archive for June, 2012

Avoid Trigger Execution – Special Cases

June 12, 2012 Leave a comment

Quick blog post on how to avoid trigger execution during some adhoc activity.

Today I needed to update around 12K rows in table which further had after update trigger. As a special case we didn’t want trigger to be executed after update. I knew I could do it by creating temp table in update  statement and then alter trigger to return if that temp table exists.. We tried that and it worked great.

But I still binge’d to see if there is any other way I could accomplish this . I saw same question in one forum and saw Context_Info() function mentioned as answer. But didn’t see any example code in the post so just for testing I ran couple of script to verify it and it works great too and also much cleaner way to get it done.

Scripts :

CREATE TABLE trtable (ID INT, gn VARCHAR(10))

GO

INSERT INTO trtable (ID, gn)

SELECT 1, ‘ab’
UNION ALL

SELECT 2, ‘bc’
UNION ALL

SELECT 3, ‘cd’

GO

–SELECT * FROM trtable

Trigger Script :

CREATE TRIGGER trigtest
ON trtable
AFTER UPDATE
AS
declare @c varbinary
set @c = CONTEXT_INFO()

If @c = 0x01
Begin
Return
End

EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘local’,
@recipients = ‘njandwani@local.com’,
@body = ‘Trigger executed.’,
@subject = ‘blah-blah-and-blah’;
GO

Now if you run below dml on trtable it won’t execute trigger code (In my trigger email part).

SET CONTEXT_INFO 0x01
INSERT INTO trtable(ID, Gn)

SELECT 4, ‘ef’

So here it is. Another way to skip trigger execution in some special cases. I didn’t get time to do further testing about behaviour in explicit transaction
or behaviour in other type of triggers. Will try to check it out if got time.

-NJ

Advertisements
Categories: Uncategorized

DDL Trigger to Automate Permission

June 8, 2012 Leave a comment

This bit is related to object permission in DB and how did we automate it.

In our DB env we use role based security for every db .Whenever any SP or function is deployed in DB we grant appropriate permission
to role in that DB. Direct access to tables\views is not granted to application logins. We needed to come with solution to grant appropriate
permission when ever new sp\function is created in DB. So DDL triggers come to rescue.

Here is small script I deployed in my DB so that we don’t miss permission grant to new objects. Works great for us. No more worries about permission.

CREATE TRIGGER Trg_Module_Perm_DDL
ON DATABASE
FOR CREATE_FUNCTION, CREATE_PROCEDURE
AS BEGIN
SET NOCOUNT ON;
DECLARE @ObjectName AS NVARCHAR (100),
@fntype AS VARCHAR (5),
@EventType AS NVARCHAR (50),
@Grant AS NVARCHAR (300);

SET @EventType = EVENTDATA().value(‘(/EVENT_INSTANCE/EventType)[1]’, ‘nvarchar(50)’);

SELECT @fntype = type FROM sys.objects
WHERE name = EVENTDATA().value(‘(/EVENT_INSTANCE/ObjectName)[1]’, ‘nvarchar(100)’);

SELECT @ObjectName = EVENTDATA().value(‘(/EVENT_INSTANCE/ObjectName)[1]’, ‘nvarchar(100)’);

IF @EventType = ‘CREATE_PROCEDURE’
BEGIN
SELECT @Grant = ‘Grant Execute on ‘ + @ObjectName + ‘ TO DBRole’;
END
IF @EventType = ‘CREATE_FUNCTION’
BEGIN
SELECT @Grant = CASE @fntype
WHEN ‘FN’ THEN ‘Grant Execute on ‘ + @ObjectName + ‘ TO DBRole’
WHEN ‘IF’ THEN ‘Grant Select on ‘ + @ObjectName + ‘ TO DBRole’ ELSE ‘None’
END;
END
BEGIN TRY
–Print @Grant
IF @Grant <> ‘NONE’
BEGIN
EXECUTE (@Grant);
END
END TRY
BEGIN CATCH
RAISERROR (‘Error while Granting permission to the proc %s’, 16, 1, @ObjectName);
ROLLBACK;
END CATCH
END

If you have multiple DBs then this needs to be deployed to every DB you need automated permission grant. You can generate separate script for every
DB or even can keep one script for every DB. For one script you might need to modify above script by adding variable for rolename and conditionally
setting value of this variable based on DB_Name() function output. Here is updated script.

CREATE TRIGGER Trg_Module_Perm_DDL
ON DATABASE
FOR CREATE_FUNCTION, CREATE_PROCEDURE
AS BEGIN
SET NOCOUNT ON;
DECLARE @ObjectName AS NVARCHAR (100),
@fntype AS VARCHAR (5),
@EventType AS NVARCHAR (50),
@Grant AS NVARCHAR (300),
@RoleName AS NVARCHAR (20);

SET @EventType = EVENTDATA().value(‘(/EVENT_INSTANCE/EventType)[1]’, ‘nvarchar(50)’);

SELECT @fntype = type FROM sys.objects
WHERE name = EVENTDATA().value(‘(/EVENT_INSTANCE/ObjectName)[1]’, ‘nvarchar(100)’);

SELECT @ObjectName = EVENTDATA().value(‘(/EVENT_INSTANCE/ObjectName)[1]’, ‘nvarchar(100)’);

IF DB_Name() = ‘DB1’
SET @RoleName = ‘DB1Role’;
ELSE
IF DB_Name() = ‘DB2’
SET @RoleName = ‘DB2Role’;
ELSE
SET @RoleName = ‘DBRole’;
IF @EventType = ‘CREATE_PROCEDURE’
BEGIN
SELECT @Grant = ‘Grant Execute on ‘ + @ObjectName + ‘ TO ‘ + @RoleName + ‘;’;
END
IF @EventType = ‘CREATE_FUNCTION’
BEGIN
SELECT @Grant = CASE @fntype
WHEN ‘FN’ THEN ‘Grant Execute on ‘ + @ObjectName + ‘ TO ‘ + @RoleName + ‘;’
WHEN ‘IF’ THEN ‘Grant Select on ‘ + @ObjectName + ‘ TO ‘ + @RoleName + ‘;’ ELSE ‘None’
END;
END
BEGIN TRY
PRINT @Grant;
IF @Grant <> ‘NONE’
BEGIN
EXECUTE (@Grant);
END
END TRY
BEGIN CATCH
RAISERROR (‘Error while Granting permission to the proc %s’, 16, 1, @ObjectName);
ROLLBACK;
END CATCH
END

Now we can add all kind of fancy stuff here. For eg. you can send all this information including grant statement to logging table or grant permission
to other login\role if needed. Couple of small tweaks to above script will do that for you..

So here is my simple method to automate permission grant for some common DB routines. There are several methods to do this so use whatever works best
for you.

Any comments , suggestions , improvements welcome.

–Neeraj

Categories: Sql Server