February 3, 2016 at 12:23 pm
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
January 21, 2018 at 8:04 am
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
January 22, 2018 at 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.
January 22, 2018 at 10:52 am
Steve Jones - SSC Editor - Monday, January 22, 2018 9:38 AMGRANT INSERT ON dbo.DBSchema_Change_Log TO PUBLIC
GRANT CONNECT TO guestThis 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.
January 22, 2018 at 2:03 pm
Didn't need it in my testing. Just INSERT and I could have a normal user insert via DDL trigger.
January 22, 2018 at 2:05 pm
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.
January 23, 2018 at 9:43 am
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.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply