|
|
Posted by Erland Sommarskog on 06/04/05 23:41
Liam Caffrey (liam.caffrey@gmail.com) writes:
> So an unchanged checksum does not guarantee that the data has not
> changed. That seems to be clear.
>
> However, I would guess that the chances of (a) someone making a change
> to a script and (b) that change delivering functionality that can be
> compiled and be meaningful, is fairly, if not very remote, as to be
> irrelevant.
>
> Does anybody use the SQL Server checksum for maintaining audit control
> of database objects?
When I was building a set of tables for our load tool, one thing I
looking for, was a way to track if someone had loaded an object outside
the load tool. That is, the load tool would track all loads in a table,
but would of course not track if someone tried to load an object through
Query Analyzer or somesuch.
I was indeed considering using
CHECKSUM_AGG(BINARY_CHECKSUM(syscomments.text)). The reason I eventually
didn't was that I wanted to support all three of SQL 6.5 and SQL 7 and
SQL 2000, and the troublesome here was SQL 7, which does not have
the checksum functions, but does have ALTER PROCEDURE which does not leave
a trace in sysobjects.
So eventually, I found no other way to rely on the columns crdate and
schema_ver in sysobjects. I found that for each ALTER PROCEDURE,
schema_ver is incremented with 16. This is not very documented, though.
Since I had to go with this on SQL 7, I figured I could just as well
use it on SQL 2000 as well.
On SQL 2005 it's easier. There is now a sys.objects.modified_date,
horray!
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
Navigation:
[Reply to this message]
|