Sql-server – SQL Server instance level action tracking

sql serversql server 2014ssms

How can we track actions happening at instance level in SQL Server 2014 .e.g. would like to know who created database, who changed recovery model, who setup backup strategy, who gave permissions to particular user etc..

Is there any native way or external tool effective for this scenario. Please let me know.

Best Answer

Actions happening at the instance level can be monitored by one of the following:

  1. SQL Server Audit feature

https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-action-groups-and-actions?view=sql-server-ver15

DATABASE_CHANGE_GROUP - who created database
SERVER_PERMISSION_CHANGE_GROUP - who gave server-level permissions to particular login
DATABASE_PERMISSION_CHANGE_GROUP - who gave database-level permissions to particular user
... etc

  1. Extended Events feature

Provides some additional benefits and is highly configurable, allows to track 1000+ different kind of events and will cover most your needs

  1. DDL and Logon triggers
Related Question