Sql-server – Which permissions are required to view Windows Event Logs in SSMS

To track object use with the SQL Server Audit feature, it’s necessary to set up the auditing. In order to do so, an audit object must be created first.

This can be done using SQL Server Management Studio or T-SQL.

The next step is to set up the auditing in the particular database on specific objects. This is where availability of the SQL Server Audit feature is required on the database level (the database level auditing is available in SQL Server Enterprise and Developer editions only).

To continue setting up the auditing, it’s required to create a database level audit specification. Such database level audit specification will belong to the audit object previously created.

Although SQL Server provides a built-in feature (the View Audit Logs context menu option of an audit object) to view captured information, this is not a convenient way for creating comprehensive reports, and it provides basic filtering only.

So, in order to provide tracked information for any deeper analysis or documenting, use the fn_get_file_audit SQL Server function to read repository .sqlaudit files used by the audit object.

SQL Server manages its own security. Just because you have local Windows administrator rights doesn't automatically grant you access.

That said, there are two approaches to get back in, using local administrative rights:

1. Impersonate the NT AUTHORITY\SYSTEM account. There are two methods to do this, as I blogged about here. These methods allow you to leave the server running while recovering access. If there is no login for that system account, you have to use the second method (which is easier, and if you don't care about downtime, the one you should use anyway because it's safer).

2. Restart the database engine service in single user mode, which does grant local administrators sysadmin rights over the instance. Do what you need to do, then restart the instance in multi-user mode. This is the backdoor which was created precisely for the situation you're in; however, you do have to interrupt service to do it.