February 4, 2010 at 10:56 am
We are running SQL Server 2005 EE on a Veritas cluster. From time to time, the Developers complain that they are missing permissions from this table or that and this account or that. The DBAs are not changing the permissions. However, some of the Developers have privileged accounts, and several Windows Administrators have access to the cluster.
Without causing performance problems, what are our options for tracking permission changes?
February 4, 2010 at 11:13 am
query the default trace I think will get you that
SELECT *
FROM fn_trace_gettable('d:\drive\path\to\default\trace',1)
inner join sys.trace_events te on tr.eventclass = te.trace_event_id
WHERE category_id = 8 --security audit category
The problem there is that you need to know the file of the current trace (and maybe past ones)
you can get that with this query:
select value from :: fn_trace_getinfo(0)
where traceid=1 and property = 2
Craig Outcalt
February 4, 2010 at 12:46 pm
SQLBOT,
This shows some nice possibilities for us. Apparently, something is auto-deleting the old files (and I am having them restored).
Do you know how to configure the auto-deletion so that we can keep a week or so of old data on disk?
February 4, 2010 at 2:33 pm
February 5, 2010 at 9:36 am
shew (2/4/2010)
SQLBOT,This shows some nice possibilities for us. Apparently, something is auto-deleting the old files (and I am having them restored).
Do you know how to configure the auto-deletion so that we can keep a week or so of old data on disk?
this is because of the max file size and number of rollover files
settings of the default trace.
I don't know if you can change it. There are other settings you can't change, I do know that much.
You could try to select into an audit table that persists. It might be tricky to figure out when a file rolls over. It could probably be done, though....
Craig Outcalt
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply