Tracking SQL Server Database Permission Changes


Coming back after a long weekend – on a Monday morning, we found one of our services to be down in our Test environment.

Looking at the server event logs, we found the error message in the stack trace – ‘System.Data.SqlClient.SqlException: Login failed for user‘.

We tracked the issue back to a service account which got removed from the database. Once we added the account, the service was up and running.

Now the next obvious question was who/when/why made the change to the service account? Did someone manually make the change or was it caused by a deployment? Was the change intentional or just a mistake?

While troubleshooting issues, viewing the SQL Server logs is beneficial since it contains information about user-defined events and also few system events.
You will be able to find information like – when the SQL Server instance has stopped and restarted, Memory issues, Login Failures or any other potential issues. However I did not find any information relating to adding/deleting user accounts or user permission changes on a SQL Instance.

SQL Server Logs

So I shifted my focus and started looking at the default trace in SQL Server.

The default trace provides lot of information about the security and health of your SQL Server instance. It is a great resource to track changes made to your SQL instance and can be used for auditing purpose.

By default, the SQL Server Default Trace is enabled and is present in the SQL Server installation directory.

Default Trace provides detailed information on the Security/Audit Events —

Below is the list of Audit events which you can look at —

SQL Audit Events

You can execute the below script to find the security changes in your database –

However do keep in mind that SQL Server by default has five trace files. If you have a busy system, the trace files may roll over far too fast and you may not be able to view some of the changes.

Reference articles

Find Permission Changes In The Default Trace 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s