I received quite a few emails from readers who read my article on managing and scheduling DTS packages. Many readers like the idea of using a SQL Server login so that all packages are owned by the same account. Therefore a group working on the same project can edit each other's packages. However, many also asked the question of auditing. Sometime we want to know which person in a group that share the same SQL login edited the package. I think this can be useful in some situations.

In this article, I will show you how we can create an audit trail for a DTS package. This method can provide information of who modified a package when from what workstation. It is not only good for auditing DTS package changes, it can also be modified for auditing changes to other tables. In a lot of databases we manage, we all know that some tables are more important than others, such as tables for salary, social security number, etc. With very little modification, you can use this method to audit changes to those tables as well.

Background information

When we are searching for ways to do auditing, we naturally turn to triggers. Triggers are a special class of stored procedure defined to execute automatically when an UPDATE, INSERT, or DELETE statement is issued against a table or view. Triggers are powerful tools that sites can use to enforce their business rules automatically when data is modified. We will use triggers to create an audit trail for DTS packages.

As most of you know, DTS packages are stored in the MSDB database. Whenever a DTS package is created, a new record is created in the sysdtspackages table. Likewise, when a package is modified, a new record will be inserted also. The only difference is that this updated record retains the id of the package and generates a new versionid. Therefore we will just use the INSERT trigger. SQL Server automatically saves the old version of a package when it is being updated and saved. This gives you great flexibility of going back to the old version if needed.

Since SQL Server keeps old versions of a package, there is no need for us to keep the before-and-after states of the packages. Therefore, to keep track of who made modifications at what time from where, we need to use data stored in the sysprocesses in the Master database. Among the many columns in the table, the following are of particular interest: spid (SQL Server process ID), hostname (Name of the workstation), program_name (Name of application program), cmd (Command being executed, not the full SQL statements though), nt_domain (Windows domain for the client), nt_username (Windows user name), net_address (Assigned unique identifier for the network interface card on each user's workstation, NIC card number), and loginname (SQL or Windows login name).

How do we get that information from sysprocesses, you may ask. Fortunately, SQL Server provides a global variable of @@SPID. Based on @@SPID, we can find out who-is-doing-what-when-from-where from the sysprocesses table.

Parts, pieces, and putting it all together

First off, we need to create the audit trail table. You might be tempted to create this table in MSDB. I personally is not fond of that idea. I usually leave all the system databases (Master, Msdb, Model, and Tempdb) alone. For one thing, it is not a good idea to mess around with system databases. For another, SQL Server patches, service packs, and hot fixes frequently update and modify system databases. You might lose any thing you developed on those databases as a result. I have a database called DBA on every server that I manage. To the extent possible, all my admin-related activities, tables, and stored procedures are stored in this database. The DTS package audit table is no exception. However, INSERT authority needs to be granted to the login that makes DTS changes. Otherwise you will get an error. Below is the script for the audit table.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DTSAuditTrail]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[DTSAuditTrail]
GO
CREATE TABLE [dbo].[DTSAuditTrail] (
[DTSAuditID] [int] IDENTITY (1, 1) NOT NULL,
[PackageName] [varchar] (100)  NULL ,
[spid] [smallint] NULL ,
[dbid] [smallint] NULL ,
[status] [char] (60)  NULL ,
[hostname] [char] (256)  NULL ,
[program_name] [char] (256)  NULL ,
[cmd] [char] (32)  NULL ,
[nt_domain] [char] (256)  NULL ,
[nt_username] [char] (256)  NOT NULL ,
[net_address] [char] (24)  NULL ,
[loginame] [char] (256)  NULL ,
[AuditTime] [smalldatetime] NOT NULL
)
GO
ALTER TABLE [dbo].[DTSAuditTrail] WITH NOCHECK ADD
CONSTRAINT [DF_AuditTrail_AuditTime] DEFAULT (getdate()) FOR [AuditTime]
GO

As you can see, this table has an identity field, a package name field, and a timestamp field. The rest are data from sysprocesses.

Below is the trigger script on the sysdtspackages table within msdb database. One technique I want to highlight is the use of SCOPE_IDENTITY() function. SCOPE_IDENTITY() returns the last IDENTITY value inserted into an IDENTITY column in the same scope. Since the audit table has an identity field, I will use that to update the record with package name information. Using SCOPE_IDENTITY() makes the code look cleaner and simpler. We also save a few lines of code. Please note that in my case, the audit table is in the DBA database. As I said earlier, you can put this table into msdb database. In that case, a slight modification of this trigger is needed. In any case, you want to make sure that the ID that modifies the DTS package has INSERT authority to the newly created audit table.

CREATE TRIGGER [PackageChangeAudit] ON [dbo].[sysdtspackages]
FOR INSERT
AS
--Declare a variable for package name
declare @PackageName varchar(100)
--Insert values into the audit table. These values come from master..sysprocesses
--based on @@SPID
insert into dba..DTSAuditTrail (cmd, dbid, hostname, net_address, nt_domain, nt_username,
program_name, spid, status, loginame)
select cmd, dbid, hostname,  net_address, nt_domain, nt_username, program_name, spid, status, loginame
from master..sysprocesses where spid = @@SPID
--Get the package name
select @PackageName=name from inserted
--Update audit table with package name. Note SCOPE_IDENTITY( ) function is
--used here to make the code cleaner
update dba..DTSAuditTrail set PackageName = @PackageName
where dba..DTSAuditTrail.DTSAuditID = SCOPE_IDENTITY( )

After the trigger is created, all modifications will be recorded into the audit table. You can search that table using package name, login ID, workstation name, and timestamp. Hopefully it can provide you with a good idea of changes made to the packages you manage.

Conclusion

In this article, I showed you how to use @@SPID to create an audit trail for DTS packages. If you are interested in DTS automation like automating DTS logging. You can read this article I wrote last month.