DDL Triggers in SQL Server 2005

Introduction

In 2002 the US Congress enacted the Sarbanes-Oxley Act which required a company to have solid change manage procedures for IT systems. One of the requirement is to know who did what and when to the database objects. There are several ways to achieve this in SQL Server 2000 - C2 level auditing or implementing third party tools. However, SQL Server 2005 introduced a new feature called DDL triggers to satisfy the Sarnes-Oxley.

Triggers are not new to SQL Server. But prior to SQL Server 2005 triggers were DML triggers, which were raised only when there is an INSERT,UPDATE or DELETE action. A new table, database or user being created raises a DDL event and to monitor those, DDL triggers were introduced in SQL Server 2005.

Implementation

Following is the syntax for DDL triggers.

CREATE TRIGGER trigger_name ON { ALL SERVER | DATABASE } [ WITH [ ,...n ] ] { FOR | AFTER } { event_type | event_group } [ ,...n ] AS { sql_statement [ ; ] [ ...n ] | EXTERNAL NAME <> [ ; ] }

DDL triggers can be created in either in the Database or the Server. If you want to monitor table creations and drops, you should create DDL trigger on the database, while to monitor operations like database creations you should create a DDL trigger on the Server.

Take a simple example of creating a database.

CREATE DATABASE [DDL_TRIGGERS_DB]

Let us assume that we want to log all the new table creations. We will log all the events in some other database called DDL_Trigger_Log in a table which has following schema.

CREATE TABLE [dbo].[tblDDLEventLog](

[ID] [int] IDENTITY(1,1) NOT NULL,

[EventTime] [datetime] NULL,

[EventType] [varchar](15) NULL,

[ServerName] [varchar](25) NULL,

[DatabaseName] [varchar](25) NULL,

[ObjectType] [varchar](25) NULL,

[ObjectName] [varchar](25) NULL,

[UserName] [varchar](15) NULL,

[CommandText] [varchar](max) NULL,)

Then we need to create a DDL trigger so that all the relevent event data is updated in the above table. Follwing will be the DDL trigger.

CREATE TRIGGER [ddltrg_CREATE_TABLE_LOG] ON DATABASE -- Create Database DDL Trigger

FOR CREATE_TABLE -- Trigger will raise when creating a Table

AS

SET NOCOUNT ON

DECLARE @xmlEventData XML

-- Capture the event data that is created

SET @xmlEventData = eventdata()

-- Insert information to a EventLog table

INSERT INTO DDL_Trigger_Log.dbo.tblDDLEventLog

(

EventTime,

EventType,

ServerName,

DatabaseName,

ObjectType,

ObjectName,

UserName,

CommandText

)

SELECT REPLACE(CONVERT(VARCHAR(50), @xmlEventData.query('data(/EVENT_INSTANCE/PostTime)')),

'T', ' '),

CONVERT(VARCHAR(15), @xmlEventData.query('data(/EVENT_INSTANCE/EventType)')),

CONVERT(VARCHAR(25), @xmlEventData.query('data(/EVENT_INSTANCE/ServerName)')),

CONVERT(VARCHAR(25), @xmlEventData.query('data(/EVENT_INSTANCE/DatabaseName)')),

CONVERT(VARCHAR(25), @xmlEventData.query('data(/EVENT_INSTANCE/ObjectType)')),

CONVERT(VARCHAR(25), @xmlEventData.query('data(/EVENT_INSTANCE/ObjectName)')),

CONVERT(VARCHAR(15), @xmlEventData.query('data(/EVENT_INSTANCE/UserName)')),

CONVERT(VARCHAR(MAX), @xmlEventData.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)'))

GO

Then create a table and retrieve data in the tblDDLEvetnLog table:

You can see that all the necessary information, we will look more details about DDL triggers.

Database Triggers

As specified before, DDL triggers are executed whenever you create, drop or alter an object at the database level. Users, tables, stored procedures,views, service broker objects like queues , functions and schemas are the objects which fall into the database objects.

In a DDL trigger you can specify the trigger options (ie the operations that need to be triggered). In the above example, it is specified to execute the triggers when a new table is created. However, rather than specify each operation, there are DDL event groups that you can specify. In that case the trigger will be executed for all the operations in that event group. For example, if you specified DDL_DATABASE_LEVEL_EVENTS instead of CREATE_TABLE all the events for CREATE_TABLE, ALTER_TALBE and DROP_TABLE that trigger will be executed hence all the events will be logged.

That trigger will look like below;

CREATE TRIGGER [ddltrg_CREATE_TABLE_LOG] ON DATABASE

FOR DDL_DATABASE_LEVEL_EVENTS

AS

/* Your code goes here */

At the end of the article, you will find the all the existing trigger events with it's highrachy. If you specificed an event, the trigger will be excuted for all the subsequent events.

EVENTDATA is an important function in DDL triggers. The EVENTDATA() function will be raised whenever a DDL trigger is fired. Output of the EVETNDATA() function is in XML format. The following is the XML format of the EVENTDATA() with example.

You can use above tags to suit your requirments.

Let us see what are the options that we can use with EVENTDATE() functions.

Apart from monitoring table creations. another requirment for DBAs is to prevent users creating tables or any other objects which does not conform to a standard. For example, if you want to stop users from creating tables which do not have prefix tbl, you can use following DDL trigger.

CREATE TRIGGER [ddltrg_CheckCreateTable] ON DATABASE

FOR CREATE_TABLE

AS

SET NOCOUNT ON

DECLARE @xmlEventData XML,

@tableName VARCHAR(50)

SET @xmlEventData = eventdata()

SET @tableName = CONVERT(VARCHAR(25), @xmlEventData.query('data(/EVENT_INSTANCE/ObjectName)'))

IF LEFT(@tableName, 3) <> 'tbl'

BEGIN

RAISERROR ( 'You cannot create table name without starting with tbl',

16,- 1 )

ROLLBACK

END

GO

After creating above DDL trigger, if you try create a table like the following,

CREATE TABLE Customer

(

ID INT,

Desccription VARCHAR(50)

)

You will get below error and table will not be created because of the ROLLBACK statement specified in the trigger.

Msg 50000, Level 16, State 1, Procedure ddltrg_, Line 17

You cannot create table name without starting with tbl

Msg 3609, Level 16, State 2, Line 1

The transaction ended in the trigger. The batch has been aborted.

It is important to remember is that unlike DML triggers, in DDL triggers you won't find INSTEAD OF triggers. Instead of using INSTEAD OF triggers, you can write the trigger so that it triggers instead of the opreration. Because of this, in DML triggers you do not have to roll them back. As there is no such an option for DDL triggers, you have insert a ROLLBACK which might be a bit expensive.

You can extend the DDL trigger to include stored procedures , functions and for schemas.

Also, if you want to stop users doing ALTER_TABLE during peak hours, you can do this by using the PostTime XML tag of EVENTDATA().

Server Triggers

Server DDL triggers fire when server operations are performed. For example, if you want to audit create database operations, the following trigger can be used.

CREATE TRIGGER [ddlsvrtrg_CREATE_DATABASE_LOG] ON ALL SERVER

FOR CREATE_DATABASE

AS

/* Your code goes here */

This trigger will also have the same EVENTDATA() function with same output XML format. Hence you will have all the options that database triggers have.

Enable or Disable Triggers

As in DML triggers, you have the option to Enable or Disable DDL triggers (for both server and database triggers)

DISABLE TRIGGER ddltrg_CREATE_TABLE_LOG

ON ALL SERVER

GO

ENABLE TRIGGER ddltrg_CREATE_TABLE_LOG

ON ALL SERVER

GO

Trigger Execution Order

When there are several triggers, you can define which trigger to execute first and last. There is a system stored procedure named sp_settriggerorder to set the priority. This is the same stored procedure which you can use to set priority for DML triggers as well.

sp_settriggerorder [ @triggername = ] '[ triggerschema. ] triggername' , [ @order = ] 'value' , [ @stmttype = ] 'statement_type' [ , [ @namespace = ] { 'DATABASE' | 'SERVER' | NULL } ]

From @order parameter you can set either first or last, which is the order of the trigger execution. The @namespace parameter can be set either DATABASE or SERVER depending on whether the DDL trigger is a database or server dependent trigger.

System Tables

It is often necessary to know where the triggers are saved. In case of database DDL triggers, the information is stored in sys.triggers and sys.trigger_events. The sys.triggers view contains information like trigger name, create date etc and sys.trigger_events view contains the for which events those triggers are going to execute.

SELECT *

FROM sys.triggers

SELECT *

FROM sys.trigger_events

In case of Server DDL triggers, you have to use sys.server_triggers and sys.server_trigger_events.

SELECT *

FROM sys.server_triggers

SELECT *

FROM sys.server_trigger_events

Improvements

Eventhough there are 100+ events included for DDL triggers, there are few important events. Specifically events for database backup, database restore, and SQL Server Job related.

Audit Data Modifications

Audit Data Modifications
This is one DBA’s tale of auditing his databases for modifications. There will be many different ways that you can approach this task, and reach a solution. Your solution may be similar to or different from mine. I will explain how I reached my solution, and give you some good ideas of how to accomplish a similar task, for your own systems.

I must mention that my database houses financial data, and needs to be compliant with Sarbanes Oxley requirements. I’m not fanatical about SOX, but have accepted its existence, and have tried to coexist with it, peacefully. At the common denominator between what the auditor wants, and what I need to monitor, I think I found a happy medium. I need to know what modifications occur in my databases, so that I can provide an auditable trail to the auditors, and I can reasonably assure myself that no unauthorized modifications occur on my system.

With SQL Server 2008, some more functionality will be built into the DB system to allow for auditing these types of modifications. I look forward to seeing how this will affect my system, but for now, this system works with SQL 2000 and SQL 2005. C2 auditing is always an option. Third party applications provide another alternative solution. I chose to create a home grown solution that allowed me the flexibility and control over monitoring.

General Overview of the Audit Data Modifications System
My shop is under the watchful eye of Sarbanes-Oxley. Not only do I, as a DBA, need to know that modifications occur in a controlled manner, modifications need to remediated and an auditable trail provided of my actions as well as other actions. I may perform a task that updates the database, but was requested to do so by the Change Control process. I may need to remove records that are inhibiting normal process flow in the system, but there should have been a business owner request for this as well. I may be tracking down a bug in the system, but a Change Control process or bug tracking process must be followed in this circumstance as well.

First, let’s define what is meant by Data Modifications. I refer too Insert, Update and Delete statements. Not structural changes to database objects; Tables, Views, Stored Procedures, Functions. Not job alterations. Not modifications to users or logins. This was rather hard to do, and differed from Database Changes I have previously written about, in that these are only data alterations. Most are ok, but removing data from certain tables can negatively impact a system. As well as remove data that is important.

Next, I do not presume to dictate how your Change Control process functions. Let’s assume that you do have some process in place, and that it is not only understood but followed. If it is not followed, disciplinary actions can and should occur to those that breach the Change Control process.

Next, the system needs to be minimally impacting, constant, and separate from the main server being monitored. We want business to continue as usual, but we need an overshadowing system that will know of all modifications that occur on the monitored db. I have chosen SQL Server Trace to perform these actions. Not the Profiler, though you can use this to do the same task, but in a manual way. Trace allows me to decide what type of events I want to monitor, and then gather specific columns of information about these events. I can drill down to very specific events; even filter specific columns for specific data results. This ability allows me to determine what my business needs are, and ignore things that I do not care to monitor. The files generated by the Trace system are rather small, and can easily be used in a variety of ways once generated. I use the files to keep as a backup, and import them to a system to report.

Since we need to monitor each server, all day long, but want periodic results, I chose a window of 1 hour to be the largest period monitored. Thus a trace will generate a file for a 1 hour block, and then start a new file. Within each hour file, I can know fairly quickly of any breaches of process. Instead of waiting until the next day to process a day’s worth of data. So each hour a job fires off a trace, and then quits and repeats. Also each hour, another job will import this trace file into a database for reporting.

The way I decided to accomplish this task was as follows.
1. I create a central Monitor Server with SQL Server installed on it.
2. For each server I wish to monitor, a linked server will be created and tested.
3. The Monitor Server will have a job that fires off, and reaches over to another server (via linked server), and starts a trace.
4. The trace file will be stored locally, on the Monitor Server. The ability to create a file will need to be configured and tested.
5. When the trace file has been finished, a process will import this data to a Data Modifications database for reporting.
6. Scrubbing of the data will occur, and data removed based according too business needs and rules.
7. Someone will review this data
8. Each item will be justified. A ticket or other Change Control documentation will be associated with an approved action.
9. Non approved actions will be raised as potential breaches of the Change Control System.
10. Lots of yelling and screaming and finger pointing will occur, until the modification is understood and justified.
11. Notification Reports can be generated for the Auditors of interested parties.

Justification
Email Reports
At one point, a simple email containing all the modifications was sent out to needed parties. With many multiple servers to be monitored, this would prove to be a rather large email report. A one by one email was not an option. Reporting Services was implemented to produce a report, and thru a subscription, emails were sent out, with links to the report. Soon, we embedded the report in the email, and this caused problems with too large of emails. In the end, the data is simply reviewed by the reviewer in SQL.

Linked Server Justification
There are always issues with linked servers. I chose this easy method, because we can ensure security on the Monitor Server, disallowing anyone from accessing it, and being able to gain access to the monitored servers it monitors. We control the password changes on the monitored servers, and can alter them at will. We have a mix of SQL Server 2000 and 2005, and did not want to go the mail route on each server. Centrally gathering the data is easily accomplished with a central Monitor Server, and linked servers is an easy way to accomplish this task.


System Overview
On the Monitor Server, there is an Audit database. Within this database, there are various tables, a few jobs, and a few stored procedures.

Tables
[AuditDataModificationDetail]
This is the main table containing data gathered from the Trace files from other servers. The data will sit here and be reported on.
The fields in the [AuditDataModificationDetail] table are as follows.
[SPID],
[StartTime],
[EndTime],
[LoginName],
[HostName],
[DBUserName],
[DatabaseID],
[DatabaseName],
[ServerName],
[ApplicationName],
[EventClass],
[ObjectType],
[ObjectID],
[ObjectName],
[TextData],
[TargetLoginName],
[NTUserName],
[NTDomainName],
[Success]
This information is the core data we retrieve from the server that experienced the Data Modification. It will provide ample information for you to investigate the infraction. These are all fields that are available to be traced for the Data Modification events I will describe later. They should be self explanatory. If not, BOL can describe them. The ServerName field is important to maintain, so when you monitor multiple servers you can know where the breach occurred.

[AuditDataModificationHistory]
As each trace file is created, on each monitored server, I log this into a history table. This data is used to keep track of the status of the Trace file, and to document, historically, the traces performed. This information can be used to troubleshoot issues later.
The fields in the [AuditDataModificationHistory] table include the following:
[AuditDataModificationHistoryID],
[TraceFile],
[Imported],
[DatabaseServerName]
The [AuditDataModificationHistoryID] field is an Identity to make the row unique. The [TraceFile] indicates not only the name of the trace file created, but the path it was created in. There is an [Imported] field that signals the state of the Trace File. The [DatabaseServerName] field indicates which Monitored Server we are watching. As a file is created, the initial value of the [Imported] field is set to 0 (zero), indicating it was created. When the file is successfully imported, it is set to 1. Other values will indicate various errors that occurred during processing. See the stored procedure [sp_ProcessDataModificationTraces] for more details.

[AuditDataModificationTraceHistory]
To keep track of the last run status of a Monitored Server, I created a simple table with the following fields.
[Servername],
[Date],
[Success]
These let me indicate the ServerName, last run date, and the status of success or failure of the last run. This is rarely used, but does come in handy when I need to spot check the system. If I think that a trace has not fired off appropriately, I can reference this table for the last point in time occurrence of a trace, and determine if it’s running or not. I consider this a cheating way to quickly monitor status.

[AuditDataModificationConfig]
This contains a ServerName field and an Enabled flag. These two simple fields allow the system to know if a Server is enabled to be processed by this system. Simple. Select the Enabled records, and viola! You now have the result set of servers to process.

[AuditDataModificationsClientConfig]
This is an addition to the system that allows me to group servers by a Client and Type of Server. At one point, I had trouble with the job failing that processes the servers part way thru, because of a linked server failure. This would cause all subsequent servers to not be monitored. This table was introduced to group servers together. The fields of this table are as follows:
[ClientName]
[ServerName]
[ServerType]
[Order]
This table allows me to group a client’s servers together, and further subgroup them by ServerType. For example, I have a client with three types of servers, Workflow servers, Mailroom servers and Dataentry servers. I can call each of these separately to be processed, by Client and ServerType. Or I can call them to be processed all at once, by simply choosing the Client parameter, leaving the ServerType blank. Multiple clients can be called separately. Or I can call all servers to be processed by leaving off both parameters. These features allowed me to setup multiple steps in the processing job (described later) and continue to the next step on failure, allowing better processing.

[AuditDataModificationsEMailConfig]
This table allows me to setup multiple reports to be generated to needed parties. In most cases, I have narrowed it down to 2 groups of modifications. Those modifications that were performed by the DBA and those not performed by the DBA. The table contains the following fields:
[ServerName]
[ReportType]
[Enabled]
[ToRecipients]
[CCRecipients]
[BCCRecipients]
[Query]
For each [ServerName] I can have multiple [ReportType]. If this is an [Enabled] record, it will be processed by a job described later. The rest of the fields describe the type of email report that is being sent out. To whom it will go is indicated in the Recipients fields. The last field is the [Query] field, wherein you will write the specific sql to pull out the criteria you want. I have hard coded parameters for date into mine that will process a specific datetime range. A sample sql statement is below.

select *
from Audit.dbo.vAuditDataModificationDetail
where StartTime Between @DateFrom and @DateTo
and LoginName in ( 'DOMAIN\TJay.Belt')
and ServerName = 'ServerName'

As you can tell from the above query, I am only looking at a certain rate range. Then, I further filter it by Login name, in this case, my domain login name. Add as many of these names as your DBA team supports. Then I am specifying the [Servername]. This should produce all modifications that I have made within the timeframe on said server. As you can imagine, the combinations are endless to what you will want to monitor.
In our case, we basically want to sanity check what the DBA’s have done, and what anyone else may have done. So those are the two queries we use against all the monitored servers involved. This way we know what the DBA’s have done, and what anyone who may not be authorized to make modifications has done.

[AuditDataModificationFilterConfig]
This table allows me to configure a given Servername with specific things to look for.
In most cases, it is the same types of filter items we look for. But the flexibility of this allows you to pick and choose items for each Servername. This table contains the following fields:
[ServerName],
[ColumID],
[LogicalOperator],
[ComparisonOperator],
[Filter],
[Enabled],
[ID]
This let’s me filter by each possible [ServerName]. You will notice that I cannot spell Column, and I honestly didn’t notice this for years. Please change this in your system. The [ColumID] is relative to the trace columns available. Refer to BOL for more detail on these. The [LogicalOperator] is a parameter in the system stored proc [sp_trace_setfilter]. As it says in the BOL, ‘Specifies whether the AND (0) or OR (1) operator is applied. logical_operator is int, with no default.’ The [ComparisonOperator] specifies the type of comparison to be made. The [Filter] value is what we are truly filtering on. Some examples are as follows:
The most important ones are the DML statements below
%Insert %
%Update %
%Delete %
Other examples are
%SQLAgent%
%DTS Designer%
%.Net SqlClient Data Provider%
Many more filterable items can be used here, allowing your specific needs to be met in your systems. I always included a web service account that we deemed safe to make modifications, so we would filter it out, and ignore those changes.

There is also an [Enabled] field, allowing you to turn on and off these filters.



Views
I created a view to help me out in displaying the data a bit cleaner. This view convert the fields into varchars of specific sizes, so that they are displayed in a standard fashion in various reporting locations. The view that I have used is as follows:
vAuditDataModificationDetail

Stored Procedures
These stored procedures started out living in the master db, and got named sp_ for that reason, so they could be called from elsewhere. But as the project grew, I moved them to an Audit database, but failed to rename them. There are other naming issues I have noticed along the life of this system. In places I name objects ‘Modification’ and in others ‘Modifications’. If I had the gumption to go back and redo it all, I would. But seeing how it is now in production, I lazily leave it the way it is. Take this chance to update it yourself.

[sp_StartDataModificationsTrace]
This proc will start up the trace on the remote monitored server. There are a load of parameters to the proc that allow for a lot of customization. A name will be created for the file, based on the Servername and a Datetime stamp. History is created when the trace is started, so we can track the individual trace file status. We call the Trace system stored procedures. Once all the events have been set and the columns have been set, we set any filter values in the table [AuditDataModificationFilterConfig]. There are also some specific hard coded filtered values in the proc. Once this has all been done, the trace is ready to start, and we call sp_trace_setstatus to get it going. The trace will typically run for 1 hour, and quit. A new trace will start at that time from a job that will be described later.

[sp_StartAllDataModificationTraces]
This procedure is used to start the entire lot of enabled servers you want to monitor. It will cycle thru the tables [AuditDataModificationsConfig] and [AuditDataModificationsClientConfig], and find all the enabled servers, based on the client param you pass in, or it will simply get them all, if no client param was used. After getting each [Servername], it will call [sp_StartDataModificationsTrace] and start the traces, as described above. History is added to the [AuditDataModificationTraceHistory] table.

[sp_ProcessDataModificationsTraces]
Hourly, we will be not only starting a trace, we will be processing the traces that have previously run. We start this process by looking into the table [AuditDataModificationHistory] and we find the records that were not imported yet. We can do this for all servers, or specify a ServerName. We grab the filename, and path from the history table, and check for file existence prior to loading the trace file. If the file exists, we update the History table with a status indicating we are importing the data. We then use the function ::fn_trace_gettable to import th data from the trace file. This data gets loaded into a staging table first, then into the table [AuditDataModificationDetail]. As errors occur, we will note this in the [Imported] status field in the history table. If no errors occur, we simply update this status field at the end, letting all know that it was successfully imported.

In our case, we copy these trace files to multiple locations for other processing. A copy is sent to a central clearing house for reporting. Another copy is sent to a third party within our group that reviews our actions, mainly looking at the DBA’s actions. [sp_ProcessAllDataModificationsTraces]
As with the above proc that starts all the traces, this proc will process all, cycling thru the available servers by looking for enabled Servers in the table [AuditDataModificationsConfig] and calling the above proc [sp_ProcessClientDataModificationTraces] to get things going. This allows for easy calling of 1 proc from a job to get all things going.

[sp_ProcessClientDataModificationsTraces]
This proc allows me to process all the traces for a particular client. I can pass in a Client and ServerType as params, and it will cycle thru all available records from the table [AuditDataModificationConfig] and calls [sp_ProcessDataModificationTraces] for each client. This is the proc that is used from within the job that allows me to specify a subset of servers to process, allowing the job to continue if a particular Client has issues, so that the entire job does not fail.

[sp_PurgeDataModificationDetail]
This stored proc allows you to customize the items that you would like to be purged from the trace file, and not stored on the db server. Now, this seems a bit hypocritical to purge data that shows modifications, for an audit… but hear me out, there is a valid reason. Since the trace will pick up all actions that occur for certain events, and it is indiscriminate at which actions it picks up, some of these may be acceptable to you. Some actions that occur, you may want to ignore, as they have been proved to be safe actions. You can remove them at this stage, and not have false positives in your reporting. For example, any time a web user updates a table, that event will be captured and stored. However, since this is a non issue, I do not want to store these actions. You may find others as well in your organizations. Add these here, but be careful that you do not purge too much valid data.

[spSendEmailAllDataModificationDetail]
The purpose of this stored proc is to cycle thru all enabled records in the table [AuditDataModificationEMailConfig] and calls the next proc, which will email the necessary individuals information about this server and its actions. From the config table, we pull vital information to send to the next proc. This uses a cursor to process thru the records, and allows for a single execution of this proc from a job, to satisfy the entire set of enabled servers to have their information emailed out.

[spSendEmailDataModificationDetail]
This proc will create a formatted email message, inserting the values of the params passed in, and send the email out to the intended parties. The params for this are as follows :
@ToRecipients varchar(4000),
@CCRecipients varchar(4000) = null,
@BCCRecipients varchar(4000) = null,
@ServerName sysname,
@Date Datetime,
@ReportType sysname,
@Query nvarchar(4000) = null
The Recipients params are self explaining. The Servername will be used to display which server this data report refers too. The date is the time for when the report was ran. The ReportType is a description set in the config table, explained above. The query is what you supplied to produce this data. If no results are returned, no attachment of data will be in existence in the email, and the email will indicate there were no modifications. If modifications did occur, and data was returned from your query, this data will be attached in simple ascii format to the email.

This isn’t the best way, or only way to report on this information. But when I created this, and supported just a few servers, it was easy and did the trick. Later, I dismissed these procs, and used Reporting Services and subscriptions to send me data. Now, this task has been outsourced to a third party that handles it differently.


Jobs
[_Audit - Data Modifications / Client]
I feel impressed to justify my naming convention. I use the underscore to keep these jobs at the top of the list of jobs, sorted alphabetically. This is a strange habit I picked up years ago. Since the job is an Audit job, I have labeled it as such with the term ‘Audit’. ‘Data Modifications / Client’ processes the Data Modification by Client. The first incarnation of this job had 1 step. But with the implementation of the Client/ServerType options, I added as many steps as I had for that combination. This allows the job to continue processing, even if a single Client/ServerType combination failed.

Each step calls the proc [sp_StartAllDataModificationTraces] and passed in specific Client and ServerType params. Some forgo the ServerType altogether, and simply call the Client param. Each step has been given the ability to continue to the next step on success or failure, to ensure it process all it can, even if it receives individual failures.

[_Audit - Process Files / Client]
This job will call the process procedures for each Client and ServerType you want. Multiple steps, like the job above has, are needed to ensure proper calling to each combination that you may have in your system. The last step of this job is to call the Purge Data Modification proc. This will clean up any unneeded data that was imported.

The Rest of the Process
The rest is up to you. All other steps that you take from this point are not dictated by system jobs or timetables, other than the ones you indicate in your job requirements as a DBA. You will now have data being stored in an audit database. You can act upon it or not. It’s your choice. Since we have a third party reading this information, we do not act upon it as it comes into the system. We get daily reports from this individual that describes the items that have no explanation. They may be a DBA’s actions or otherwise. We can research the data in these tables to determine what happened, who did it, when, etc. And then act upon that information.

The important fact is that there is now detailed data describing modifications in your monitored systems, sitting, waiting for you to do something. You or someone ‘must’ remediate them. Regardless of the reality of your solution, you need to document why these modifications occurred. Who did what, when it was done, and what was done. Mark this item as resolved and give it good notes. You will find yourself referring to this information in the future, when you need too. Why did so-and-so make X modification? You can now research it, and even mine data on its past modifications. Now, when the Auditor’s come knocking on your door, you have a solution in place that allows you too easily provide them a report that shows all Data Modifications, and any remediation actions taken. The sample script for this article can be found here.

Automate Audit Requests

User Review
Periodically, an internal auditor would stop by my desk and ask me to show them all the users and permissions on a particular database. This is easily gathered, and most of us know how to do this. I produce this list, and send it on to the requestor. The next question is ‘What has changed?’. I do not have this information, and have to tell them I do not know.

So, after a couple iterations of this (I will not admit how many) I finally devised a simple way to store this information to adequately respond to this question. A snapshot of the user information is gathered from time to time, and stored into a history table. The next time a request comes in I can compare the current values to the historical ones. This would become tedious as it usually involved some spreadsheets or query results, and manually reviewing the data, looking for new records, changed records and so on. When I would produce these two sets of data (current and 1 historical snapshot) and give the data back to the auditor, they were initially happy, until they realized all the time that would be involved to perform an adequate review of the two sets of data.

The next question would invariably be, is there a way to automate this? There is always a way to automate anything, I would respond, and skulk back to my DBA hole and pound out some more code. After more review of the results I produced and even more back and forth between me and the auditor, we finally decided which fields we needed to see, the differences we should show, etc. I will now try to explain the system that we devised to assist us in this simple, yet complex endeavor.

System
We basically want to know what the users looked like at a point in time. Compare it to the current state of the users, and show differences. Differences will be defined as: new records, changed records, removed records and old records. We want to be able to dynamically include any number of new and existing servers into this system, though we started with a single server. We want to be able to display these results for a period of time, and allow reporting to occur based on the results.

Since we already have a monitoring server setup, this was the perfect place to locate this system. We already use linked servers to connect too and monitor all our remote servers, so we will continue in this vein for this system. Justifications can be read in other articles I’ve written about monitoring with linked servers. A configuration table needs to indicate which servers we will be monitoring. Some stored procedures will need to use dynamic sql, cycle thru the config list of servers, and call them to gather data, stage the data, and then do comparisons against this data. Then resulting data can be reported on.

That’s the high level description. I will now go through all the objects, with more detail.


Tables
DatabaseRolesConfig
This table will contain a ServerName and an Enabled flag. This allows us to setup multiple servers to be monitored, and enable and disable them at whim. The fields for this table are as follows:
ServerName sysname not null,
Enabled bit not null default 0

DatabaseRolesStaging
This table will allow us to store data we have pulled down from all the remote servers. Once staged, we can query it and process it to our desires. The fields in this table are as follows:
Type sysname null default '',
ServerName sysname null default '',
DBName sysname null default '',
LoginName sysname null default '',
UserName sysname null default '',
db_owner varchar(3) null default '',
db_accessadmin varchar(3) null default '',
db_securityadmin varchar(3) null default '',
db_ddladmin varchar(3) null default '',
db_datareader varchar(3) null default '',
db_datawriter varchar(3) null default '',
db_denydatareader varchar(3) null default '',
db_denydatawriter varchar(3) null default '',
denylogin int null default '',
hasaccess int null default '',
isntname int null default '',
isntgroup int null default '',
isntuser int null default '',
sysadmin int null default '',
securityadmin int null default '',
serveradmin int null default '',
setupadmin int null default '',
processadmin int null default '',
diskadmin int null default '',
dbcreator int null default '',
bulkadmin int null default ''
All the fields accept a default of blank, for reporting purposes. Not all fields for every row will have a value. This eliminates the display of null values in the report. This is just a simple step I chose to make life easy.

DatabaseRolesHistory
This table is identical to the staging table, and will hold the processed data from the last execution for comparison to the next execution.

DatabaseRolesArchive
This table is identical to the staging table, except that it has an added Identity field for uniqueness, and a [Date] field that will contain the date of the archived data. This will be a holding area for all the data processes and displayed from past executinos. The extra fields are as follows:
ID integer not NULL IDENTITY(1,1),
[Date] datetime not null default getdate(),

DatabaseRolesDisplay
This table is similar to the above two tables. We’ve added an Identity field for uniqueness. A Version field to keep track of the previous version (Type). This will be the table that contains the processed data, grouped by type, and cleaned up. We will report from this table, as it will have the result set of data after processing. The fields of this table are as follows:
ID integer not NULL IDENTITY(1,1),
Version VARCHAR(8) not null,

Stored Procedures
sp_GetDBRoles
Thie procedure be passed a ServerName, DatabaseName and a UserName. The last two params were never implemented. But the ServerName determines which server we will be pulling data from. We use dynamic sql to pull information from the syslogins from the remote server, and then store it in the DatabaseRolesStaging table, with a ServerName and Type included.

Then we create a cursor that will cycle thru each database in sysdatabases, except a few.
Inside the cursor, we will dynamically call sql that pulls more data from sysmembers, sysusers and syslogins, retrieving those users that have roles set. This data is also stored in the staging table also.

We will next use dynamic sql to pull more data from sysusers and syslogins, retrieving those users that do not have roles set. This data is also stored in the staging table.

sp_GetAllDBRoles
This will cycle thru the config table DatabaseRolesConfig and call the sp_GetDBRoles proc for each server that is enabled to be processed.

sp_ProduceDatabaseRolesResults
Since we have previously gathered records into the DatabaseRolesStaging table, we can now compare these results to some other tables of historical data. We have a history table called DatabaseRolesHistory that contains the last set of data we gathered about Users.

1. We select the new values, and insert them into a memory table @DatabaseRolesStaging with a flag indicating they are ‘new’.
2. 
   

1. We select the old values that have changed, and insert them into the table @DatabaseRolesStaging with a flag indicating they are ‘old’.
2. We update the ‘new’ values that have changed. We determine this if there is a new record, and an old with some of the same values (Servername, DBName, UserName and LoginName). These are updated in the table @DatabaseRolesStaging with a flag indicating they are ‘changed’.
3. We find the records that were removed from the DatabaseRolesHistory table, compared to the DatabaseRolesStaging table. These records are inserted into the table @DatabaseRolesStaging with a flag indicating they are ‘removed’.
4. Records that were simply altered are removed from the memory table @DatabaseRolesStaging.

This resulting data is now labled and ready to display to the requestor. We process the data from the @DatabaseRolesStaging table, and order it with ‘Old’ and ‘Changed’ being first, then the ‘Removed’ records, followed by the ‘New’ records. This just helps in the viewing of the data, with the important ones being first, and so on. This resulting data is dumped into a real table called DatabaseRolesDisplay, and will live there until the next execution of this process. This allows me to reselect from this data when needed between executions. I used to have this simply returned once as part of the proc call, but would tend to need to look at the data subsequently; this solves that need.

sp_ProcessDatabaseRoles
This stored procedure takes a ServerName as a parameter. If you use this option, it will call the procedure sp_GetDBRoles for just that ServerName. If you leave the ServerName blank, then it will call sp_GetAllDBRoles and process all enabled ServerNames from the config table. This proc will then get the user data into the staging table as described above. It will then call the sp_ProduceDatabaseRolesResults procedure, which will process the data in the staging table, comparing it to the historical data. Then the data in the history table will be pumped into the Archive table. The history table will be truncated. The current staging data will be pumped into the history table, and there await the next execution. This is the Gem of the sytem, taking all other parts into account, and doing it all for you. This can be called singly when the Auditor requests it. Or you can schedule it to run as a job, and simply query the resulting data in the DatabaseRolesDisplay table. There are many options you now have to follow, depending on your own needs.

This system will allow you to gather user information, stage it, and store it historically. Allowing you the chance to see back into the past at a snapshot of what once was. No more will you be stymied by auditors or others with the questions of what users do we have in the system, and how do they compare to a year ago. You have the data, you are empowered, you are the DBA in control and informed.

I hope that this system will help you gather the needed data and have it onhand to help out with your user reviews.

Download Article Code