0

we are using SQL2012 Enterprise Edition and have a small problem with one of our DBA (5 persons with more than 100 instances). is it possible to check who has changed given, denied or revoked permissions and when this was done? I could not find any trigger or audits.

Thanks for your help and best regards from HAMBURG in GERMANY

mleko
  • 11,650
  • 6
  • 50
  • 71
user3537351
  • 1

1 Answers1

0

Two choices spring to mind here:

  1. Use SQL Server Audit, and create audit specifications to capture the events that you are interested in (see http://technet.microsoft.com/en-us/library/cc280386.aspx for more details)
  2. Use server level triggers to capture all DDL events (see http://www.mssqltips.com/sqlservertip/2085/sql-server-ddl-triggers-to-track-all-database-changes/ for an example of how to do this)

We basically use the second approach across our production instances for audit purposes, and to record a complete history of all schema changes across all databases so that we can accurately identify what has changed (and by whom, and when) to assist with production troubleshooting of issues.

Paul McLoughlin
  • 2,283
  • 1
  • 15
  • 15