Auditors on my back
-
I need advice on how to handle the following situation: We recently came through an "internal" audit. One of the tasks that came out of it for me is to keep an audit trail of the "insert", "delete", "update" activity of the 5 or so technical users we have access to Query Analyzer. Your first thought of "they shouldn't have any kind of write access to production data" is a moot point. They have it and looks like they're keeping it.
SQL Profiler did a pretty good job of giving me what I need. Only it truncates the text data a bit too soon. The other thing is I need something to set up and let it go, not something I have to remember to run everyday.
I am only looking to capture date, time, user logon and the sql that was run.
I would like to explore using triggers, but I need to know where to get the sql text. I read that you can run 2 SQL Profiler traces having the first with the default to exclude SQL Profiler turned off , then running a second one to trace what you really want to trace. The article said that the first trace would tell you where the second trace was getting its info. I didn't find that to be true........sounded like a good idea......
Any insight on this would be appreciated.
Thanks in advance.
-
Assume it's SQL 2000 since you specified Query Analyzer. If you're going to go through all of that, why not just turn on C2 Auditing:
USE master
EXEC sp_configure 'show advanced option', '1'
RECONFIGURE
EXEC sp_configure 'c2 audit mode', 1
RECONFIGURE
Here's an article: http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlc2.mspx
You can load C2 Audit logs into Profiler to view them as well.
-
C2 auditing is resource intensive because it probably captures more than what his auditors are asking for. However, it will certain do the job. Look at doing server side traces (Profiler actually invokes these behind the scenes). You can build out your trace in a stored procedure which, using sp_procoption, you can set to autostart whenever SQL Server starts. If you tinker a bit with SQL Server Agent and stopping and starting new traces, you could stop the trace once a day, restart the trace, move the previous day's trace files off, etc.
K. Brian Kelley
@kbriankelley -
Look at the function fn_get_sql in the BOL. That will get you the full SQL that is run (unlike DBCC INPUTBUFFER which only gets the first 255 characters).
-SQLBill
-
I agree that the OP will get more info back than he needs with C2 auditing (DDL, logins, security info, etc.) I haven't seen any head-to-head comparisons but I imagine the type of auditing the OP requested is going to be resource-intensive whether you're using triggers, traces (which C2 auditing also uses), or just about anything else. I just offered C2 as the shortest path to get the job done
It could also work as an intermediate solution while he designs and sets up a more strongly customized auditing system.It would be interesting to see the performance differences between C2 and other forms of auditing.
-
Any request such as you have received has to be subject to a cost / risk analysis. You should also verify with the auditors and your mangement that any solution you propose will be considered robust enough to be relied on.
There are a number of log analysis products that can show change activity on given databases or users. Your auditors may expect a solution of this quality rather than a home-grown script, so beware of spending a lot of time on something that may work but be rejected.
C2 auditing should give the results your auditors have requested, but it has a performance cost. It may be worth benchmarking the performance hit and converting it to a monetry amount. Your management should then present the cost of compliance to the auditors, and they should both agree if the cost of compliance is within or exceeds the risk they wish to avoid. At the end of the day, the auditors may agree that the solution costs more than the problem, and decide to abandon the idea of tracing usage.
If you show a professional approach in giving some costed solutions to the audit requests, it can only enhance your reputation.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
-
I have a job running in SQL Server Agent which does this, uses sysprocesses and DBCC INPUTBUFFER, I tried SQL Trace and a few other approaches, mostly 6 of one or a half dozen of the other.....this takes a few seconds and I load it into a table which I check every now and again......
-- CREATE YOUR OWN PROCEDURE HERE
-- GET RUNNING PROCESSES FROM MASTER..SYSPROCESSES
SELECT getdate() as Date_Time,spid,
convert(char(19),last_batch) as last_batch,
substring(hostname,1,10) as hostname,
substring(loginame,1,12) as loginame,
CAST (' ' AS varchar(255)) AS SQL
INTO#SYSPROCESSES
FROM master.dbo.SYSPROCESSES t1
JOIN master.dbo.SYSDATABASES t2
on t1.dbid = t2.dbid
-- CREATE TEMP TABLES FOR DATA FROM DBCC INPUTBUFFER
SELECT CAST (' ' AS varchar(15)) EVENTTYPE, CAST (' ' AS varchar(15)) PARAMETERS, CAST (' ' AS varchar(255)) AS EVENTINFO
INTO#inputbuffer
TRUNCATE TABLE #inputbuffer
SELECT CAST (0 AS INT) AS SPID, CAST (' ' AS varchar(15)) EVENTTYPE, CAST (' ' AS varchar(15)) PARAMETERS, CAST (' ' AS varchar(255)) AS EVENTINFO
INTO#inputbuffer_ALL
TRUNCATE TABLE #inputbuffer_ALL
-- USE CURSOR TO LOAD TEMP TABLES WITH SQL FROM DBCC INPUTBUFFER
declare @spid INT
declare NEW_CURSOR insensitive cursor for
select spid
from #SYSPROCESSES
open NEW_CURSOR
fetch next from NEW_CURSOR into @spid
while (@@fetch_status != -1)
begin
TRUNCATE TABLE #inputbuffer
insert #inputbuffer EXEC('dbcc inputbuffer (' + @spid + ')')
INSERT #inputbuffer_ALL(SPID, EVENTINFO)
SELECT @spid, EVENTINFO FROM #inputbuffer
fetch next from NEW_CURSOR into @spid
end
deallocate NEW_CURSOR
-- UPDATE SYSPROCESSES TABLE WITH SQL CODE
UPDATE #SYSPROCESSES
SET SQL = T2.EVENTINFO
FROM #SYSPROCESSES T1, #inputbuffer_ALL T2
WHERE T1.SPID = T2.SPID
-- SELECT PROCESSES
-- INSERT INTO YOUR TABLE
SELECT DISTINCT * FROM #SYSPROCESSES
where hostname ' '
-
PS I borrowed part of this CODE somewhere on this site so if anyone recognizes it thanks very much!!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply