Sql-server – Create an Audit trail on SQL Server 2008 to trace all privilege users

auditsql serversql-server-2008

I need to create a trace of all the commands issued by members of SysAdmin or SecurityAdmin server Roles. I figured, I could use Profiler to do that but it will not comprise all the added members. I also checked the new Audit feature and yes, I can trace membership change on these roles but I will not get all their activity log.

All I see now is to reactivate the trace with a DDL trigger on Server Roles membership change.

Do someone brighter than me have a better idea ?

Best Answer

You might be looking for something like Change Data Capture.

Change data capture is designed to capture insert, update, and delete activity applied to SQL Server tables, and to make the details of the changes available in an easily consumed relational format. The change tables used by change data capture contain columns that mirror the column structure of a tracked source table, along with the metadata needed to understand the changes that have occurred.

Change data capture is available only on the Enterprise, Developer, and Evaluation editions of SQL Server.

Related Solutions

ORA-09925: Unable to create audit trail file

Try this: connect to SQL*Plus, then do:

show parameter audit_file_dest

then exit SQL*Plus and do:

cd <path displayed above>
ls -ld .

What are the permissions? Ownership?

Perhaps a mounted filesystem has gone missing, and you don't have write permissions on the underlying mountpoint?

Sql-server – SQL Server 2008 datetime index performance bug

There's no mystery, you get a good(er) or (really) bad plan at basically random because there is no clear cut choice for the index to use. While compelling for the ORDER BY clause and thus avoid the sort, you non-clustered index on the datetime column is a very poor choice for this query. What would make a much better index for this query would be one on (serial_number, test_date). Even better, this would make a very good candidate for a clustered index key.

As a rule of thumb time series should be clustered by the time column, because the overwhelming majority of requests are interested in specific time ranges. If the data is also inherently partitioned on a column with low selectivity, like it seems to be the case with your serial_number, then this column should be added as the leftmost one in the clustered key definition.

Best Answer

Related Solutions

ORA-09925: Unable to create audit trail file

Sql-server – SQL Server 2008 datetime index performance bug

Related Question