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 ?
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.
You can use stored procedures and functions for instance. Tracking who-saw-what can be done through specially developed stored procedures and functions. In order to use this auditing method, access to the database must be limited through use of stored procedures (allowing EXEC statements only, while prohibiting all queries and DML operations). The result from a query is returned by a stored procedure, while at the same time the stored procedure logs the access in an appropriate auditing repository table along with additional information of interest (e.g. the time of the execution) However, this auditing method requires T-SQL programming and additional maintaining of the stored procedures and functions (e.g. in case of the database schema change). Moreover, ad hoc queries executed by trusted parties (e.g. members of the sysadmin role) easily override the rule that SELECT statements must be parsed by a stored procedure, and therefore cannot be tracked
Beside traces, you can also use the SQL Server Audit feature, introduced in SQL Server 2008. The feature utilizes the Extended Events technology, and audits both server and database events. However, auditing on database level is supported by the Enterprise and Developer editions only
Using SQL Server Audit as the method for tracking execution of SELECT statements produces less overhead than the trace technology, but depending on how busy the database is, an impact on server performance might occur. Either T-SQL or SQL Server Management Studio options can be used to set up the SQL Server Audit feature
