DDL Trigger to Track All Database Changes? Permissions

Question

Post reply

DDL Trigger to Track All Database Changes? Permissions

azenon

Ten Centuries

Points: 1162
More actions
February 3, 2016 at 12:23 pm

#308288

Looking to implement the server level trigger below to track all DDL changes in the instance. How do I get the trigger to insert in the DBAudit db table for any user without giving each user access to the DBAudit database?
ALTER TRIGGER [SchemaChange_LogInTable_DDL]
ON ALL SERVER
FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE, CREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION, CREATE_TRIGGER, ALTER_TRIGGER, DROP_TRIGGER
--Fires only for CREATE / ALTER / DROP on Store Procedures Functions Triggers
AS
DECLARE @eventInfo XML
SET @eventInfo = EVENTDATA()
INSERT INTO DBAudit.dbo.DBSchema_Change_Log VALUES
(
REPLACE(CONVERT(VARCHAR(50),@eventInfo.query('data(/EVENT_INSTANCE/PostTime)')),'T', ' '),
CONVERT(VARCHAR(50),@eventInfo.query('data(/EVENT_INSTANCE/LoginName)')),
CONVERT(VARCHAR(50),@eventInfo.query('data(/EVENT_INSTANCE/UserName)')),
CONVERT(VARCHAR(50),@eventInfo.query('data(/EVENT_INSTANCE/DatabaseName)')),
CONVERT(VARCHAR(50),@eventInfo.query('data(/EVENT_INSTANCE/SchemaName)')),
CONVERT(VARCHAR(50),@eventInfo.query('data(/EVENT_INSTANCE/ObjectName)')),
CONVERT(VARCHAR(50),@eventInfo.query('data(/EVENT_INSTANCE/ObjectType)')),
CONVERT(VARCHAR(MAX),@eventInfo.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)'))
)
GO

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Database admin(DBA) SSCertifiable Points: 5373 More actions · Answer 1

Hi,

Is it implemented? If yes please let me know even i'm looking for same kind of request..

Thanks in advance!!

Best Regards,

SQL server DBA

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 2

GRANT INSERT ON dbo.DBSchema_Change_Log TO PUBLIC GRANT CONNECT TO guest

This is the easiest way. I am , in general, not a fan of granting rights to public, but in this case, this is probably OK. I enable guest, and then anyone maps over to guest and data is inserted.

Phil Parkin SSC Guru Points: 246962 More actions · Answer 3

Steve Jones - SSC Editor - Monday, January 22, 2018 9:38 AM
GRANT INSERT ON dbo.DBSchema_Change_Log TO PUBLIC GRANT CONNECT TO guest
This is the easiest way. I am , in general, not a fan of granting rights to public, but in this case, this is probably OK. I enable guest, and then anyone maps over to guest and data is inserted.

Hi Steve. This article suggests that SELECT is required, in additional to INSERT, on the log table. Can't see why, though.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 4

Didn't need it in my testing. Just INSERT and I could have a normal user insert via DDL trigger.

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 5

I suspect Aaron used that in testing. For audits, I typically wouldn't have normal users able to read the audit records. INSERT only, with SELECT given to admins/security staff.

ScottPletcher SSC Guru Points: 101101 More actions · Answer 6

My (strong) preference is instead to use EXECUTE AS on the CREATE TRIGGER:

ALTER TRIGGER [SchemaChange_LogInTable_DDL]
ON ALL SERVER
WITH EXECUTE AS 'big_cheese_login'
FOR ...
...

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".