June 16, 2008 at 11:51 am
OK - I'm feeling pretty clever and just have to share. I've been working on some database auditing using DDL triggers and finally found the set of parameters that I liked. I set up a central table that all the server and database level audits would write to so that it would be easy to see who changed what, where, and when from a central location. I used certificates to sign the stored procedure that does the table insert so that I would not have to grant access to my audit database to users at large. I wanted it to be as simple and reusable as possible, so what I ended up with was one table to store all the information, one signed stored procedure to insert into the table, 1 server level trigger for server audits and 1 database level trigger script that needed to be run against each database requiring auditing.
But that wasn't dynamic enough. I wanted the database triggers to be automatically created in EVERY database from ONE script. That way I could very easily update the trigger definition in once place, run a single script to deploy, move the script from server to server, and know that it would run regardless of which databases were present or missing. It is easy enough to copy and paste a long script and set the new "USE [dbname]" in front of each one, but then you have to make sure you update each section when you make a change, and it will not work on another server that has different databases. sp_MSForEachDB doesn't work well, since you have to use 'USE [DBName];CREATE TRIGGER...>' and CREATE TRIGGER has to be the first words in the batch. Google (my good old friend) didn't seem to help much with the issue, no matter how much I begged.
But finally I did it anyway.
I created a sp_ procedure in the Master database that contained the CREATE TRIGGER script I wanted run. Then I used EXEC sp_MSForEachDB 'EXEC ?.dbo. ' to execute that CREATE TRIGGER script in every database on the server... AND IT WORKED. I'm so happy. I now have one script that sets up my audit table (if it does not exist), creates the certificate, permissions, procedures and triggers required and gets everything up and running.... regardless of the names of the databases on the box!
You may now reply with all the mistakes I have made and how this might have been done better, but I warn you - you will not burst my incredible bubble and this happy little feeling I have for finally accomplishing what once seemed impossible.
🙂
Chad
June 16, 2008 at 11:55 am
Hot spot? I'm for decoupling things like this. What if you need to move a database to a new server because it's getting busy? I would have avoided an all in one solution for this reason.
Honestly I'd probably have driven this from command line, sending in the script for each database, using T-SQL to build a list of databases to call against.
Still, sounds like you built a neat solution.
June 16, 2008 at 12:30 pm
I'm using Event Notifications to log DDL changes. In Event Notifications all events and event groups can be defined on the server scope. So with this solution you do not need to create anything in the user databases.
http://msdn.microsoft.com/en-us/library/ms189540.aspx
http://msdn.microsoft.com/en-us/library/ms180824.aspx
I have some code if you're interested.
Ola Hallengren
June 16, 2008 at 12:52 pm
Thanks Steve! Help me understand your concern about a hot spot. Are you thinking about centralizing the DDL auditing for a single server, or aggregating multiple servers together? Perhaps our environment is just too small, but we don't expect a lot of DDL and are most interested in tracking adhoc changes done outside release windows.
Putting the auditing within each database would avoid problems created by a DB move at the cost of having to query each database to see what objects had been modified. With the script I created, moving a database to a new server that doesn't have the auditing set up would result in a trigger failure since it would not have the procedure and table it was expecting to log to. That would essentially rollback any DDL change attempts within the database until we either set up the auditing or dropped the DDL trigger. DML statements should still work fine though. There is an interesting chicken/egg conundrum here - attempting to drop the DDL trigger causes the DDL trigger itself to fire. If the audit structures are not there, the drop trigger will rollback, leaving the audit trigger in place. That can be overcome by altering the trigger to be empty before the drop. (Or can you disable then drop a DDL trigger? I'll have to look.) Still, as far as front-end applications are concerned, they are not running DDL and should work fine, and if the DB was audited on the old server we probably want the auditing set up on the new server too.
It would have been much easier to use C#, SMO, or a batch file to loop through the creation, but we already have a process for releasing new .sql scripts and this fits very nicely into that without adding another process that would need to be approved and remembered. To be honest, at this point it might have been easier to just manually write all the scripts and take the time to put the USE DATABASE in at the appropriate places, but I've learned a new way around the "XXXXX must be the first statement in a query batch" error, and that's worth something.
Ola - I would love to see your code. I haven't used the server broker before so using it didn't even cross my mind. Is there much overhead to doing it this way? It looks like it would be a lot more flexible and allow us to monitor even more than what we can now.
Thanks,
Chad
June 16, 2008 at 1:38 pm
-- First make sure that service broker is enabled in the current database
SELECT [name], is_broker_enabled FROM sys.databases
GO
-- Create an audit table
CREATE TABLE dbo.Audit ( AuditID int NOT NULL IDENTITY,
PostTime datetime NULL,
EventType sysname NULL,
LoginName sysname NULL,
UserName sysname NULL,
ServerName sysname NULL,
DatabaseName sysname NULL,
ObjectType sysname NULL,
SchemaName sysname NULL,
ObjectName sysname NULL,
EventData xml NULL,
CONSTRAINT PK_Audit PRIMARY KEY CLUSTERED (AuditID ASC))
GO
CREATE PROCEDURE dbo.AuditReceive
AS
BEGIN TRY
DECLARE @AuditMessage TABLE (EventData xml)
BEGIN TRANSACTION;
RECEIVE CAST(message_body AS XML)
FROM dbo.AuditQueue
INTO @AuditMessage
INSERT INTO dbo.Audit (PostTime, EventType, LoginName, UserName, ServerName, DatabaseName, ObjectType, SchemaName, ObjectName, EventData)
SELECT CAST(EventData.query('data(//PostTime)') AS varchar(23)),
NULLIF(CAST(EventData.query('data(//EventType)') AS sysname),''),
NULLIF(CAST(EventData.query('data(//LoginName)') AS sysname),''),
NULLIF(CAST(EventData.query('data(//UserName)') AS sysname),''),
NULLIF(CAST(EventData.query('data(//ServerName)') AS sysname),''),
NULLIF(CAST(EventData.query('data(//DatabaseName)') AS sysname),''),
NULLIF(CAST(EventData.query('data(//ObjectType)') AS sysname),''),
NULLIF(CAST(EventData.query('data(//SchemaName)') AS sysname),''),
NULLIF(CAST(EventData.query('data(//ObjectName)') AS sysname),''),
EventData
FROM @AuditMessage
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH
GO
-- Create a queue to receive messages
CREATE QUEUE dbo.AuditQueue
WITH STATUS = ON, ACTIVATION (STATUS = ON, PROCEDURE_NAME = dbo.AuditReceive, MAX_QUEUE_READERS = 1, EXECUTE AS Owner)
GO
-- Create a service on the queue that references the event notifications contract
CREATE SERVICE AuditService
ON QUEUE dbo.AuditQueue ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification])
GO
-- Create a route on the service to define the address to which Service Broker sends messages for the service
CREATE ROUTE AuditRoute
WITH SERVICE_NAME = 'AuditService', ADDRESS = 'LOCAL'
GO
-- Create the event notification
CREATE EVENT NOTIFICATION AuditEventNotification
ON SERVER FOR
DDL_TABLE_EVENTS -- Here you can add other Event Groups and Events that you're interested in
TO SERVICE 'AuditService', 'current database'
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply