Hello All,

I am charged with using SQL Profiler for the purposes of auditing the use of
SQL accounts with elevated privileges. These are essentially accounts which
get checked out when needed. Corporate auditors have requested a monthly
report.

We run SQL 2005 on Windows Server 2003.

Please note that I am brand new to SQL 2005.

At a minimum, we want to capture the following for every Insert, Delete,
Modify to data, as well as any configuration changes:

UserID Start Time The
SQL Statement
Login Name Login SID NT User
name
Host name (the client) Object Name Object Type
Server Name (The SQL Server)

Other suggestions are welcome.

I was given a printout with the above but don't know where they are in
Profiler.

If anyone can suggest an overall strategy on how to do this, that would be
great.

TIA,

Rich

rich (rich (AT) discussions (DOT) microsoft.com) writes:
Are corporate auditors computer savvy? I mean will they be able to read
what comes out of Profiler?

To start with, you should not run this from Profiler, but a server-
side trace. To set up a server-side trace is not difficult: you set up
the trace in Profiler, and the export the script. The reason for is
that a server-side trace is much leaner on resources. Unfortunately, the
export facility is somewhat buggy with regards to multiple filters, so
you need to review the script.

Since I don't know your application or database, it's difficult to say
exactly how should profile. If there is a lot of access from stored
procedures you may need to trace on statement level. But constantly
running a statement-level trace, even if only filtered for some accounts
is a bit too much overhead.

One very important thing: you need the OriginalLogin column. In SQL 2005
you can impersonate antoher user with EXECUTE AS, in which case the
LoginName may change to the impersonated user and mask the real user.

For configuration changes, I think using DDL triggers is better than
profiling.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx