THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

John Paul Cook

Auditing database source code changes

Auditing changes to database source code can be easily implemented with a database trigger. Here’s a simple implementation of stored procedure auditing using an audit table and a database trigger. It assumes that a schema named Audit already exists. 

CREATE TABLE Audit.AuditStoredProcedures (

  DatabaseName sysname

, ObjectName sysname

, LoginName sysname

, ChangeDate datetime

, EventType sysname

, EventDataXml xml

);

Notice the EventDataXml column. Using an nvarchar column to store the source text of the stored procedure isn't satisfactory because a stored procedure can be many times larger than the maximum size of an nvarchar column. Although using nvarchar(MAX) might not provide enough space in theory, practically speaking it will. But reading the code inserted into a nvarchar(MAX) column isn't convenient at all. It ends up as one very long, unformatted string. The xml data type leaves the source code appearing like (although not exactly like, more on that later) it did when it was written to the audit table.

It can be argued that the first five columns in the table are superfluous because they exist in the complete XML stored in the EventDataXml column. Because the first five columns contain key information useful for searching, the redundancy is justified for the sake of convenience. It's far easier to read and search simple string values stored in character columns than obtain them from the EventDataXml column.

CREATE TRIGGER dbtAuditStoredProcedures

ON DATABASE

FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE

AS

 

DECLARE @eventdata XML;

SET @eventdata = EVENTDATA();

 

INSERT INTO Audit.AuditStoredProcedures (DatabaseName,ObjectName,LoginName,ChangeDate,EventType,EventDataXml)

VALUES (

  @eventdata.value('(/EVENT_INSTANCE/DatabaseName)[1]','sysname')

, @eventdata.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname')

, @eventdata.value('(/EVENT_INSTANCE/LoginName)[1]', 'sysname')

, GETDATE()

, @eventdata.value('(/EVENT_INSTANCE/EventType)[1]', 'sysname')

, @eventdata

);

The database trigger fires after the fact. When a stored procedure is dropped, there is no longer any code for the stored procedure and the EventDataXml column won't contain the code that was there before the drop. Similarly, an ALTER statement will store the stored procedure code after the change, not before the change. If this auditing scheme is implemented before any stored procedures are created and a complete history is maintained in the audit table, there would be a complete record of the entire life of a stored procedure. If the database trigger is implemented where there are preexisting stored procedures, you'd need to run the ALTER statement (but not actually change anything) on all existing stored procedures to get them logged into the audit table. If you don't do this and a change is made, you won't have a record of the original state of the code.

When the code is stored, the database trigger stores it as XML. Minor formatting changes will occur due to the encoding of special characters. For example, a > sign gets encoded as > according to standard XML formatting rules. You'll be able to figure out what changed, but restoring stored procedure code from the XML will be inconvenient because of this.

As you can see, auditing is not a replacement for source code version control. You may be able to get by with a simplistic auditing process in an environment where the body of code is relatively small and stable. Nor are database backups suitable replacements for version control. Even if you have transaction log backups and can do point in time recovery, tracking code changes by restoring from backups is inconvenient at best and just not feasible at worst.

 

 

 

 

 

 

 

Published Monday, April 12, 2010 4:41 PM by John Paul Cook

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

how to backup old sp for alter stored procedure said:

how to backup old sp for alter stored procedure

July 22, 2010 5:23 AM
 

ravindra said:

hi

The Above procedure will not work if i alter table in designer window i will get captured data like this  ALTER TABLE dbo.Table_1 SET (LOCK_ESCALATION = TABLE) how to solve this issuce please reply.

November 15, 2011 7:34 AM
 

John Paul Cook said:

For a more timely and in-depth response, please post specific syntax questions in the MSDN forums: http://social.msdn.microsoft.com/forums/en-US/transactsql/threads/

Because there are several factors that could come into play here, posting in a moderated forum is better.

November 15, 2011 7:46 AM
 

pete said:

works for very well needed for an unkown modification to certain stored procs...thx

March 26, 2014 11:49 AM

Leave a Comment

(required) 
(required) 
Submit

About John Paul Cook

John Paul Cook is both a Registered Nurse and a Microsoft SQL Server MVP experienced in Microsoft SQL Server and Oracle database application design, development, and implementation. He has spoken at many conferences including Microsoft TechEd and the SQL PASS Summit. He has worked in oil and gas, financial, manufacturing, and healthcare industries. Experienced in systems integration and workflow analysis, John is passionate about combining his IT experience with his nursing background to solve difficult problems in healthcare. He sees opportunities in using business intelligence and Big Data to satisfy healthcare meaningful use requirements and improve patient outcomes. John graduated from Vanderbilt University with a Master of Science in Nursing Informatics and is an active member of the Sigma Theta Tau nursing honor society. Contributing author to SQL Server MVP Deep Dives and SQL Server MVP Deep Dives Volume 2.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement