In this article, Application Development Manager Steve Keeler outlines an approach for determining the domain identity of a user who has initiated a stop, start, or restart request on SQL Server services. Although SQL Server contains server and database auditing functionality as part of the product, this cannot be used to determine the identity of a user changing the service state of a SQL Server instance since that operation is occurring at the system rather than database level.

I recently worked with a customer to help resolve an issue with Team Foundation Server where collection databases were not coming online following a service restart. While working on this issue, the following question was posed: “how can we identity the user(s) responsible for stopping, starting, and restarting SQL Server services?”.

Preliminary investigation into using SQL Server audit functionality yielded no solution. Checking with Microsoft database specialists, they confirmed that the closest auditing SQL Server could provide for service restarts would tie that operation to the privileged ‘sa’ identity. Checking with one of Microsoft’s Premier Field Engineers specializing in Security, Liju Varghese provided a quick overview on how to implement service level auditing.

The following sections provide details on the settings required at the operating system level to audit service management operations, in this case for the SQL Server service. This is done using group policy objects.

Continue reading here.

Premier Support for Developers provides strategic technology guidance, critical support coverage, and a range of essential services to help teams optimize development lifecycles and improve software quality.  Contact your Application Development Manager (ADM) or email us to learn more about what we can do for you.