SQL Server DDL Triggers to Track All Database Changes

By:   |   Updated: 2010-08-09   |   Comments (53)   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | > Triggers


Problem

In a perfect world, only the DBA would have sa privileges, F5 would only ever be hit on purpose, every change would go through rigorous source control procedures, and we would have full backups of all databases every minute. Of course, in reality, we deal with much different circumstances, and we can find ourselves (or overhear someone else) saying, "Oops... how do I fix that?" One of the more common scenarios I've seen involves someone editing a stored procedure multiple times between backups or within some kind of cycle, and then wishing they had version (current - 1) available. It's not in the backup yet, so can't be restored; and the user, of course, has closed his or her window without saving.

Solution

There are a lot of solutions to this issue, of course. They include tightening down server access, adopting a reliable source control system, and implementing a rigorous and well-documented deployment process. These things do not happen overnight, so in the meantime, DDL Triggers can provide a short-term fix that is both easy to implement and simple to manage. The approach is to take a snapshot of the current objects in the database, and then log all DDL changes from that point forward. With a well-managed log, you could easily see the state of an object at any point in time (assuming, of course, the objects are not encrypted).

 

So where do we start? First, I like to keep housekeeping items (monitoring, administration etc.) in their own database. This allows me to query things centrally and also to control growth separately. For this task, let's use a database called AuditDB:

CREATE DATABASE AuditDB;
GO

To keep things relatively simple, let's assume we are only interested in actions taken on stored procedures - create, alter, drop. We have a set of stored procedures already, and they are in a given state. We will need to capture that state, in addition to any changes that are made to them from that point forward. This way, we will always be able to get back to any state, including the original state.

In addition to the data specific to the actions taken on stored procedures, we can also think of several other pieces of information we would want to store about each event. For example:

  • database name
  • schema / object name
  • login information
  • host name / IP address (useful with SQL auth)

So here is the definition for a table to capture these events and the surrounding information about them:

USE AuditDB;
GO


CREATE TABLE dbo.DDLEvents
(
    EventDate    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    EventType    NVARCHAR(64),
    EventDDL     NVARCHAR(MAX),
    EventXML     XML,
    DatabaseName NVARCHAR(255),
    SchemaName   NVARCHAR(255),
    ObjectName   NVARCHAR(255),
    HostName     VARCHAR(64),
    IPAddress    VARCHAR(48),
    ProgramName  NVARCHAR(255),
    LoginName    NVARCHAR(255)
);

Yes, we could keep the table skinnier and use [object_id] instead of schema/object name, also protecting us from resolution problems due to renames. However, often stored procedures are dropped and re-created, in which case the system will generate a new [object_id]. I also prefer to use the database name to make ad hoc queries (and script automation) against specific databases easier. You can choose which metadata to rely on; personally, I'll trade the space for readability and scriptability.

Now that the table exists, we can easily grab a snapshot of our existing stored procedure definitions, leaving out some of the irrelevant auditing data, as follows (replacing 'my name' with whatever you want to display for the initial rows):

USE YourDatabase;
GO


INSERT AuditDB.dbo.DDLEvents
(
    EventType,
    EventDDL,
    DatabaseName,
    SchemaName,
    ObjectName,
    LoginName
)
SELECT
    'CREATE_PROCEDURE',
    OBJECT_DEFINITION([object_id]),
    DB_NAME(),
    OBJECT_SCHEMA_NAME([object_id]),
    OBJECT_NAME([object_id]),
    'my name'
FROM
    sys.procedures;

Now we're ready to start capturing actual changes to these procedures as they happen. You can create a DDL Trigger with the following code, that will record pertinent data to the above table when changes are made to stored procedures:

USE YourDatabase;
GO


CREATE TRIGGER DDLTrigger_Sample
    ON DATABASE
    FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE
        @EventData XML = EVENTDATA();
 
    DECLARE @ip varchar(48) = CONVERT(varchar(48), 
        CONNECTIONPROPERTY('client_net_address'));
 
    INSERT AuditDB.dbo.DDLEvents
    (
        EventType,
        EventDDL,
        EventXML,
        DatabaseName,
        SchemaName,
        ObjectName,
        HostName,
        IPAddress,
        ProgramName,
        LoginName
    )
    SELECT
        @EventData.value('(/EVENT_INSTANCE/EventType)[1]',   'NVARCHAR(100)'), 
        @EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(MAX)'),
        @EventData,
        DB_NAME(),
        @EventData.value('(/EVENT_INSTANCE/SchemaName)[1]',  'NVARCHAR(255)'), 
        @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]',  'NVARCHAR(255)'),
        HOST_NAME(),
        @ip,
        PROGRAM_NAME(),
        SUSER_SNAME();
END
GO

Note that when you create a DDL Trigger, just like a DML Trigger, it is enabled and will start working immediately. To disable it, you can run the following code:

USE YourDatabase;
GO


DISABLE TRIGGER [DDLTrigger_Sample] ON DATABASE;

And then to re-enable:

USE YourDatabase;
GO


ENABLE TRIGGER [DDLTrigger_Sample] ON DATABASE;

So now you can test the auditing capabilities by altering an existing procedure. Right-click a procedure in Object Explorer and choose Modify. Add the following line somewhere in the body:

-- testing audit

Then you can run a query against the DDLEvents table:

SELECT *
    FROM AuditDB.dbo.DDLEvents
    WHERE EventType = 'ALTER_PROCEDURE';

Assuming your system is relatively quiet, all you should see is the change above. Now to go one step further, you can examine the differences between the initial object and its most recent state using a query like this:

;WITH [Events] AS
(
    SELECT
        EventDate,
        DatabaseName,
        SchemaName,
        ObjectName,
        EventDDL,
        rnLatest = ROW_NUMBER() OVER 
        (
            PARTITION BY DatabaseName, SchemaName, ObjectName
            ORDER BY     EventDate DESC
        ),
        rnEarliest = ROW_NUMBER() OVER
        (
            PARTITION BY DatabaseName, SchemaName, ObjectName
            ORDER BY     EventDate
        )        
    FROM
        AuditDB.dbo.DDLEvents
)
SELECT
    Original.DatabaseName,
    Original.SchemaName,
    Original.ObjectName,
    OriginalCode = Original.EventDDL,
    NewestCode   = COALESCE(Newest.EventDDL, ''),
    LastModified = COALESCE(Newest.EventDate, Original.EventDate)
FROM
    [Events] AS Original
LEFT OUTER JOIN
    [Events] AS Newest
    ON  Original.DatabaseName = Newest.DatabaseName
    AND Original.SchemaName   = Newest.SchemaName
    AND Original.ObjectName   = Newest.ObjectName
    AND Newest.rnEarliest = Original.rnLatest
    AND Newest.rnLatest = Original.rnEarliest
    AND Newest.rnEarliest > 1
WHERE
    Original.rnEarliest = 1;

If you are tracking down a specific object or an object in a specific schema, you could put additional WHERE clauses against Original.ObjectName or Original.SchemaName. From here, you can take the values for "OriginalCode" and "NewestCode" and put them through your favorite diff tool to see what changes there have been. And you can also change the query slightly to retrieve the latest version of any procedure, and the version that preceded it - I'll leave that as an exercise for the reader.

What the above does not capture are other peripheral changes that can happen to a stored procedure. For example, what about moving a procedure to a different schema? You can change the DDL Trigger above in the following way to capture the ALTER_SCHEMA event:

USE YourDatabase;
GO


ALTER TRIGGER DDLTrigger_Sample
    ON DATABASE
    FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE,
        ALTER_SCHEMA
AS
BEGIN
    -- ...

And how about rename? Unfortunately in SQL Server 2005, DDL Triggers were unable to observe calls to sp_rename (or manual renames through Management Studio). In SQL Server 2008 and above, however, a rename can be captured with the aptly-named RENAME event:

USE YourDatabase;
GO


ALTER TRIGGER DDLTrigger_Sample
    ON DATABASE
    FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE,
        ALTER_SCHEMA, RENAME
AS
BEGIN
    -- ...

(In a future tip, I'll demonstrate how to restrict these additional auditing rows to specific objects or object types, so that you're not capturing all kinds of irrelevant information about changes to objects other than stored procedures.)

Some other considerations:

  • You may want to put in a cleanup routine that gets rid of "noise" more than <n> days old (but still keeping the set of objects that are important to you).
  • To validate that your auditing process is capturing all changes, you can check modify_date in sys.procedures. Of course this only works for procedures that haven't been dropped - only if they have been created, modified, renamed, or transfered to a different schema.
  • Security might be an issue, depending on what you want to accomplish. Allow me to elaborate:

    DDL Triggers will not be transparent to users - first of all, they can see them in the Object Explorer tree, so it won't be a big secret that they are there and operational. They also appear in execution plans; if users have this option enabled when they create or modify objects in Management Studio, they will see the query plan for statements such as INSERT AuditDB.dbo.DDLEvents.

    If you want to hide the definition of the DDL Trigger, you can encrypt it as follows:

    USE YourDatabase;
GO

    
ALTER TRIGGER DDLTrigger_Sample
    ON DATABASE
    WITH ENCRYPTION
    FOR -- ...

    This way, when users want to see what the trigger is doing, they will right-click to generate a script, but the following is what will happen:

    TITLE: Microsoft SQL Server Management Studio
------------------------------
Script failed for DatabaseDdlTrigger 'DDLTrigger_Sample'.
Property TextHeader is not available for DatabaseDdlTrigger
'[DDLTrigger_Sample]'. This property may not exist for this
object, or may not be retrievable due to insufficient access
rights.  The text is encrypted.
...

    But users with sufficient privileges can still disable the trigger, as described above. And you can't even capture this event, much less prevent it (which DDL Triggers are sometimes used for). For more information, see these Connect items:

    So, assuming SQL Server 2008 or above, you could use an audit specification to capture DDL events as a backup (or instead). But, given all of this, if you have to go to these lengths to prevent people from circumventing your auditing capabilites, then maybe your problems are larger and not all that technical. I suspect that in most reasonable environments, you'll sleep fine at night simply locking down the audit table.

I hope this provides a decent starting point to protect your environment(s) with DDL Triggers. However, given the manual aspect of this approach as well as its limitations, it will likely be best to consider this a short-term plan, and look into more robust source control and recovery techniques in the longer term.

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Aaron Bertrand Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and also blogs at sqlblog.org.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2010-08-09

Comments For This Article




Monday, April 20, 2020 - 6:11:05 AM - Hesam Back To Top (85416)

Question:

Step 1: Create Trigger For DDL_DATABASE_LEVEL_EVENTS,

Step 2: Alter Table In Designer Window

Step 3: SELECT SQLCommand from TreggerLogTable. SQL Engin Generated 5 Command Bellow:

1. CREATE TABLE dbo.Tmp_TableName  ...

2. ALTER TABLE dbo.Tmp_TableName  SET (LOCK_ESCALATION = TABLE)

[***Missing Command? (I Want That): INSERT INTO Purchasing.Tmp_TableName (Col1, Col2, ...)    SELECT Col1, Col2, WITH (HOLDLOCK TABLOCKX)]

3. DROP TABLE dbo.TableName

4. EXECUTE sp_rename N'dbo.Tmp_TableName', N'TableName', 'OBJECT'   

5. ALTER TABLE dbo.TableName ADD CONSTRAINT  ...

[***Missing Command?]: How Capture Bellow DATABASE INSERT Command In DDL OR DML Trigger?

INSERT INTO Purchasing.Tmp_TableName (Col1, Col2, ...)    SELECT Col1, Col2, WITH (HOLDLOCK TABLOCKX)]

Tahnks a lot.

Monday, January 20, 2020 - 11:22:25 AM - Aaron Bertrand Back To Top (83887)

@Albert, you can always derive the object_id from the info you have (you just have to consider what is more likely to change over time, if you want to keep accurate history - the name of an object, or object_id due to drop/create):


SELECT OBJECT_ID
(
  @EventData.value('(/EVENT_INSTANCE/SchemaName)[1]',  'nvarchar(255)')
  + N'.' +
  @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]',  'nvarchar(255)')
);

Saturday, January 18, 2020 - 10:39:00 PM - Albert Torres Back To Top (83858)

I do have one question you mention "we could keep the table skinnier and use [object_id] instead of schema/object name" but how do you get the object_id is not on the EVENDATA()

Thanks 

Tuesday, March 20, 2018 - 12:54:54 PM - David Waller Back To Top (75485)

 When I have the trigger enabled I keep getting multble emails for changes more then once. As in, I made a change yesterday and I got an email, then today I get anouther one. Why am I getting so many emails?

 

Thanks.

 

Friday, June 24, 2016 - 6:49:27 AM - Krishna Back To Top (41756)

 Thanks Aaron for the job suggesion but the job should check periodically from SQL Agent whether any notified = 0 are there. But my concern is, Is there any way we can make it part of the trigger or some other way just to send an email after 2 or 3 minutes after first row is created in audit table? Appreciate your time ane help.

 

Thursday, June 23, 2016 - 8:38:45 AM - Aaron Bertrand Back To Top (41746)

@Krishna Of course, instead of having the trigger send you an e-mail, just add a column to the audit table like Notified bit NOT NULL DEFAULT (0). Then have a background job that checks for new rows where Notified = 0, assemble a single e-mail containing the data from all of those rows, and then mark them as Notified = 1.

Wednesday, June 22, 2016 - 2:01:45 PM - Krishna Back To Top (41741)

Beautiful article. I created a trigger on the audit table to send an email to me. But I am getting number of emails just for one database creation because lot ALTER databases are involved. So is there any way I can get one email including all those events in one email rather than sending one email for each alter or create? 

 

Thursday, April 14, 2016 - 7:21:16 AM - vivek shimpi Back To Top (41221)

hello sir,

 is it posible to store every procedure execution time.

without alter all procedure..

 

Saturday, March 19, 2016 - 6:51:57 AM - Arun Back To Top (41006)

 

 Is there any by default audit feature is provided by MSSQL so that whenever user can perform DDL operations on Store procedure it will be tracked

Sunday, October 18, 2015 - 12:51:18 PM - jamie Back To Top (38923)

First off thank you for this post. This is exactly what I've been looking for. With that out of the way...Do you have anything else that would monitor Function changes and/or other triggers changes?

Tuesday, July 14, 2015 - 3:34:25 PM - Aaron Bertrand Back To Top (38206)

Vikash - only if the Windows login name is being exposed to SQL Server (it may not be because they're using SQL authentication or ). You can experiment with things like USER_NAME(),SUSER_NAME(),SUSER_SNAME(),CURRENT_USER, and ORIGINAL_LOGIN().

 

Tuesday, July 14, 2015 - 1:52:41 PM - Vikash Back To Top (38205)

Hi @Aaron, 

Thanks for this great post. I was wondering is there any way to keep record of windows login User Name along with IP address using SQL Database DDL trigger. Currently this code is saving user name of SQL Server using "HOST_NAME()" and IP Address from where the query got executed but i want windows login name as well.

Regards,

Vikash Singh

Tuesday, April 28, 2015 - 4:54:43 AM - Daniel Wai Back To Top (37046)

Hi, I have question for the trigger event. This is the case, I have a table with columns contains default value. When I add a new column using Design Mode in SQL Server, it triggers the ALTER TABLE event. I get the script from the EventData, but I found that the script drop the table first and recreate the table that the columns contains default value, now don`t. Is it possible the trigger event able to generate script correctly, that the table columns' default value won`t lost.

Wednesday, August 20, 2014 - 11:03:11 AM - Klaas Back To Top (34210)

And still only privileged users could execute DDL.

After:

GRANT IMPERSONATE ON LOGIN::[MyDomain\MyUser] TO [PUBLIC]

it also worked for the service-account that executed partition switch.

Now I wonder if after all that trouble I'm actually safer than granting View Server State to everyone?

Wednesday, August 20, 2014 - 9:53:11 AM - Klaas Back To Top (34207)

I think I found it:

 

I first tried:

create trigger [DDLEventsToTable]

on database

WITH EXECUTE AS 'MyDomain\Myuser'

for ddl_database_level_events

as

begin

SET NOCOUNT ON;

DECLARE @auditdata XML,...

 

but it should be:

create trigger [DDLEventsToTable]

on database for ddl_database_level_events

as

begin

SET NOCOUNT ON;

EXECUTE AS LOGIN = 'MyDomain\Myuser'

DECLARE @auditdata XML,...

Wednesday, August 20, 2014 - 9:36:40 AM - Klaas Back To Top (34204)

That doesn't seem to work:

'The user does not have permission to perform this action.'

 

Probably because "A context switch to a database user does not inherit the server-level permissions of that user" ?

http://technet.microsoft.com/en-us/library/ms181362(v=sql.105).aspx

 

 

Thursday, August 14, 2014 - 10:10:29 AM - Aaron Bertrand Back To Top (34132)

@Klaas no, you don't need to grant everyone VIEW SERVER STATE. You can:

  1. Create a login with VIEW SERVER STATE
  2. Create a database user associated with that login in (a) all of the databases where your DDL trigger exists and (b) all of the databases where the DDL trigger(s) write to
  3. Grant the database user insert on the logging table
  4. Change the DDL trigger to EXECUTE AS 'database_user'

Thursday, August 14, 2014 - 6:00:11 AM - Klaas Back To Top (34124)

Hi Aaron

 

I 've been using such a trigger for a while, but I thought it would be nice to add the IP as you do, so I pasted that part in my trigger definition. Last night some jobs failed. Since the error said 'user does not have permission...' I checked if guest had insert permission in the audit table and since it did, I tried different ways to grant necessary rights, but any alter statement kept failing.

Now I found that the error was caused by the sys.dm_exec_connections DMV. So, a user who executes a DDL statement needs the VIEW SERVER STATE permission. I think that is a very important consequence. Do you advise to grant this to everyone, maintain a list of all users who need this or do you have another strategy to prevent this trigger from breaking the system?

Thursday, April 3, 2014 - 10:38:25 AM - Aaron Bertrand Back To Top (29965)

Danny,

Are you asking if there is a generic way to write a trigger that will capture all DML activity regardless of table? No, there is nothing native that you could just turn on and call it a day.

You could add a trigger to every table you create, of course (it could even largely be handled automatically by a DDL trigger that responds to CREATE_TABLE events, but you'd have to have something watch for ALTERs as they could break the trigger code that gets generated). Or you could use features like Change Tracking, Change Data Capture, or Audit. You could even set up an Extended Events session or server-side trace to capture all DML statements, and mine that data, but that could be quite invasive.

Thursday, April 3, 2014 - 6:05:14 AM - Danny Back To Top (29954)

Hello,

is there any solution for DML instead of DDL?

Tuesday, March 11, 2014 - 12:45:15 PM - Forrest Back To Top (29718)

I have been using this code on many systems (2008R2) from all types of applications. I've had two issues...I commented one above about using select top 1 client_net_address because some apps have multiples during installs and it bombs. The other issue is that bigfix runs its installer in snapshot isolation. You may want to run the following to allow triggered events from such cases to write to the table without error.

 

ALTER DATABASE <yourloggingdb>

SET ALLOW_SNAPSHOT_ISOLATION ON

GO

Tuesday, January 28, 2014 - 12:47:34 PM - Aaron Bertrand Back To Top (29254)

Hi Elief, did you add RENAME as one of the DDL events, as I described toward the end of the article? Are you on 2008 or better? I don't know of a simple answer in 2005, maybe the default trace or a custom server-side trace...

Tuesday, January 28, 2014 - 10:41:26 AM - Elief Back To Top (29253)

This is great!!

However, when I'm trying to apply this, I found a little problem.

I'm unable to catch the rename of a table or procedure...

Anyidea on how to do that? like a trigger on sys.objects for updating names?

Thank you

Wednesday, November 6, 2013 - 3:56:03 PM - Forrest Back To Top (27432)

A few versions of this code have been pasted around the net for a couple of years, and I have been using this for a couple of years. I did have issues with MS apps(SCOM) and at least one other app failing to upgrade when the trigger is enabled.

It appears that it is better to have the query say  select top 1 client_net_address   as some app installers do some conn trickery where that query comes back with multiple results.

Tuesday, November 5, 2013 - 1:16:11 PM - Adam S Back To Top (27400)

Excellent, thanks for the find.

Tuesday, November 5, 2013 - 12:25:42 PM - Aaron Bertrand Back To Top (27398)

Adam, I'm sure you could take this script by Robert Davis:

http://www.sqlsoldier.com/wp/wp-content/uploads/Scripts/dba_CopyLogins.sql

Incorporate it into your DDL trigger, and have the DDL trigger run the generated dynamic SQL with a linked server, using EXEC linkedserver.master..sp_executesql @sql;

Monday, November 4, 2013 - 12:13:54 PM - Adam Back To Top (27383)

I am thinking about implementing this on a mirrored server to copy the logins from one server to the other.
Has anyone even attempted anything like this? I am just wondering how I will keep track of changes between the servers better then we currently are doing it.

Tuesday, July 16, 2013 - 10:38:50 AM - Saumil Back To Top (25860)

Aaron! I was able to achive the DDL logging for partitioned tables using the method suggestd by you. I have two schemas dbo and Switch. I am trying to capture the events on dbo schema and after verifying the affected objects I am executing the same DDL on Switch schema by simply capturing the DDL and replacing the schema name to Switch.

However this approach fails when a user issues statements without specifying the schema name. Any suggestions how can I go about achiveing this functionality? 

Monday, July 1, 2013 - 2:28:36 PM - Saumil Back To Top (25657)

Thanks Aaron! Appreciate your help. Will check it out and post the outcomings.

Monday, July 1, 2013 - 11:58:42 AM - Aaron Bertrand Back To Top (25650)

Saumil sure, you can simply add an IF EXISTS check to see if the object_id affected by the DDL statement exists in sys.partitions with a partition_number > 1 or see these queries:

http://dba.stackexchange.com/questions/14996/how-do-i-get-a-list-of-all-the-partitioned-tables-in-my-database

http://dba.stackexchange.com/questions/25777/how-to-check-if-there-is-partitions-in-use-in-fact-tables

Monday, July 1, 2013 - 10:59:16 AM - Saumil Back To Top (25648)

Great post Aaron. I am currently creating a similar DDL trigger on a database for partitioned tables. I need to check if DDL is executed against partitioned tables and apply the same to another table in different schema on the same database.

Your post helped me in uderstanding how the database level triggers work. Is there a way you can guide me on how to apply the same for partitioned tables? or to be more precise, can i implement database tiggers for DDL executed against specific tables?

Friday, June 7, 2013 - 7:07:49 AM - Dipo Back To Top (25333)

Many thanks for this very useful code!

Could you also provide link to a DML trigger is you have one?

Thursday, May 9, 2013 - 9:59:39 AM - mbourgon Back To Top (23836)

(two years too late, probably)

@GB - while tracking changes across your environment using the DDL triggers is definitely doable, IMHO what you really want is Event Notifications.  Uses Service Broker and can track all the things the triggers can, some trace items, and a bunch of other things as well.  I presented on this for the virtual DBA chapter of PASS a couple of months ago.  Might be worth a watch/read. http://thebakingdba.blogspot.com/2012/10/master-of-all-i-survey-using-event.html and mms://passmedia.sqlpass.org/share/dba/MasterofAllISurvey_03272013.wmv.  Jonathon Kehayias has also done some amazing stuff with EN, I'd really recommend his articles on it, starting with http://www.sqlservercentral.com/articles/Event+Notifications/68831/ . Hope this helps someone! 

Thursday, May 9, 2013 - 2:16:36 AM - Elmozamil Elamir Hamid Back To Top (23830)

Thanks for this post.

It really amazing and covering the concept clearly and in understandable way.

 

Friday, April 26, 2013 - 3:27:53 PM - Aaron Bertrand Back To Top (23583)

Amanda: sorry, this is not possible: database-level DDL needs to be caught at the database level, so you'll need a database DDL trigger on each database.

Friday, April 26, 2013 - 2:33:48 PM - Aaron Bertrand Back To Top (23581)

Joseph, could you be more specific? What errors?

Thursday, April 25, 2013 - 9:20:21 PM - Joseph Back To Top (23567)

I tried including create,alter and drop views but I'm now getting an error whenever I try to do these operations. Anyone can tell me how to modify the code to include auditing of views as well?

Wednesday, February 13, 2013 - 5:10:35 AM - Jaya Back To Top (22090) 


Really it's a great stuff.


can any one tell me how to create a table with different prfix name and with same structure, on creation of a table using ddl triggers. I also need similarly for modification of the table structure to reflect on the other table.


Waiting for the reply.

								
					

					





				


					

					

						
							Friday, June 29, 2012 - 11:11:21 AM - Amanda
						
						
						Back To Top (18267)

						
					

					

						
							
I really want to use a server level trigger for all this.  I've tried and tried, with different permissions, and it just doesn't seem to work.  I've read all the articles I can find, and it truly seems that it is just not possible to create a server level trigger to capture all DDL and Security changes.


Is this true?


 


Amanda

								
					

					





				


					

					

						
							Tuesday, June 5, 2012 - 7:04:37 AM - Johny
						
						
						Back To Top (17798)

						
					

					

						
							
Hi there,


     I have a question -- if, as you have mentioned in this article, I wanted to get the latest version of the stored procedure and the one preceding it, then how should I do it ? A little commenting for statements in the CTE would the article a world of good. Anyways, great post sans my issue :->

								
					

					





				


					

					

						
							Monday, April 30, 2012 - 3:57:43 PM - Fabio Nunes
						
						
						Back To Top (17214)

						
					

					

						
							
In your example, how to get the command would be executed by the Query Analyzer? Insert, Update, Delete tables, without specifying the table name?

								
					

					





				


					

					

						
							Wednesday, December 14, 2011 - 3:11:24 PM - Jim Curry
						
						
						Back To Top (15358)

						
					

					

						
							
Great post. How about a follow up article on how to set up policy to verify the DDL triggers exist and are enabled on all databases?

								
					

					





				


					

					

						
							Monday, December 12, 2011 - 10:29:08 AM - Scott C
						
						
						Back To Top (15337)

						
					

					

						
							
Another way the DDL trigger may not be transparent to users is if they do not have INSERT rights on the logging table.  You can enable the guest user in AuditDB and GRANT INSERT ON DDLEvents TO public to avoid problems.


I have had issues with TRANSACTION DOOMED IN TRIGGER errors on DDL changes if inconsistent settings are used for ANSI_PADDING.  Using SET ANSI_PADDING ON in all CREATE and ALTER scripts is one solution.

								
					

					





				


					

					

						
							Wednesday, October 5, 2011 - 12:39:41 PM - Jonathan Scion
						
						
						Back To Top (14778)

						
					

					

						
							
Very good article. if anybody wants a shorter way to implement the same thing - which is not free (499$) but is very reliable and has a nice GUI to go with it, check out:


 


http://nobhillsoft.com/Randolph.aspx

								
					

					





				


					

					

						
							Thursday, August 18, 2011 - 10:32:11 AM - Aaron Bertrand
						
						
						Back To Top (14444)

						
					

					

						
							
I don't understand. The trigger stores LoginName, so can't you just add it to your eventual query?

								
					

					





				


					

					

						
							Thursday, August 18, 2011 - 8:42:46 AM - Arjun
						
						
						Back To Top (14442)

						
					

					

						
							
So nice to see your reply Aaron.


I have implemented the same it's working. :)


Can we get login name also in the result set who has changed this SP?


Regards,


Arjun


 


 

								
					

					





				


					

					

						
							Wednesday, August 17, 2011 - 9:32:03 AM - Aaron Bertrand
						
						
						Back To Top (14437)

						
					

					

						
							
Sorry Arjun, the code was written for 2008+. In order to get past the syntax errors, you need to use:


    DECLARE 
       @EventData XML,
       @ip VARCHAR(32);

    SELECT @EventData = EVENTDATA();
 
    SELECT @ip = client_net_address
        FROM sys.dm_exec_connections
        WHERE session_id = @@SPID;


Sorry about that.

								
					

					





				


					

					

						
							Wednesday, August 17, 2011 - 9:04:02 AM - Arjun
						
						
						Back To Top (14435)

						
					

					

						
							
Really it's a great stuff.


can any one tell me what wrong with this trigger creation in user database. I'm receiving the following error when creating this trigger.


Waiting for the reply.    :) :)


TRIGGER code


***************************************************************


USE xxxxxxxxx;
GO


CREATE TRIGGER DDLTrigger_Sample
    ON DATABASE
    FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE
        @EventData XML = EVENTDATA();
 
    DECLARE 
        @ip VARCHAR(32) =
        (
            SELECT client_net_address
                FROM sys.dm_exec_connections
                WHERE session_id = @@SPID
        );
 
    INSERT AuditDB.dbo.DDLEvents
    (
        EventType,
        EventDDL,
        EventXML,
        DatabaseName,
        SchemaName,
        ObjectName,
        HostName,
        IPAddress,
        ProgramName,
        LoginName
    )
    SELECT
        @EventData.value('(/EVENT_INSTANCE/EventType)[1]',   'NVARCHAR(100)'), 
        @EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(MAX)'),
        @EventData,
        DB_NAME(),
        @EventData.value('(/EVENT_INSTANCE/SchemaName)[1]',  'NVARCHAR(255)'), 
        @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]',  'NVARCHAR(255)'),
        HOST_NAME(),
        @ip,
        PROGRAM_NAME(),
        SUSER_SNAME();
END
GO



***************************************************************


ERROR


***************************************************************




Msg 139, Level 15, State 1, Procedure DDLTrigger_Sample, Line 0




Cannot assign a default value to a local variable.




Msg 102, Level 15, State 1, Procedure DDLTrigger_Sample, Line 14




Incorrect syntax near '('.




Msg 137, Level 15, State 2, Procedure DDLTrigger_Sample, Line 35




Must declare the scalar variable "@EventData".




***************************************************************




Regards




 

								
					

					





				


					

					

						
							Monday, April 11, 2011 - 2:28:04 PM - GB
						
						
						Back To Top (13588)

						
					

					

						
							
This is some great stuff.


 


Quick question:  Has anyone tried to do this from a centralized server?  Meaning, I have one server that we use for monitoring/maintenance and I want the triggers to feed the data into a centralized table.  I'm having problems with the XML data type going through linkedservers.

								
					

					





				


					

					

						
							Wednesday, March 23, 2011 - 8:15:09 AM - Aaron Bertrand
						
						
						Back To Top (13285)

						
					

					

						
							
I limited the scope to stored procedures specifically, but you can certainly employ the same techniques for all object types (with a single catch-all DDL trigger, or multiple dedicated ones).

								
					

					





				


					

					

						
							Wednesday, March 23, 2011 - 4:54:52 AM - US
						
						
						Back To Top (13278)

						
					

					

						
							
Hi,


article sounds as "SQL Server DDL Triggers to Track All Database Changes", but in fact changes tracking focuses on stored procedures only. What about changes on tables, views etc. ?

								
					

					





				


					

					

						
							Tuesday, March 22, 2011 - 11:02:38 AM - Aaron Bertrand
						
						
						Back To Top (13272)

						
					

					

						
							
Sure, you can just check SUSER_SNAME() first and only log if the result is in the list you want to check.

								
					

					





				


					

					

						
							Tuesday, March 22, 2011 - 10:52:26 AM - Muhammad Azim
						
						
						Back To Top (13271)

						
					

					

						
							
Very precisely explained, 


Is there a way for establishing a DML auditing for specific non-application users?


BR

								
					

					





					
		

		


	
 
	
	
	
			
	
	

	
	
	

		

		


	

	
	








	

	
	


	





	

	
	


	



				

				


	

	
	


 


 	

	

	
	

		
		
		
get free sql tips


			

					
					
					
					  

						

						  

							

							  
							

							

							  
							

						  

						

					  

					  

						

						  

							

							  
							

							

								agree to terms