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: 734418 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: 246686 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.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Steve Jones - SSC Editor SSC Guru Points: 734418 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: 734418 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: 100940 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) A socialist is someone who will give you the shirt off *someone else's* back.