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.