One of my SQL Server users periodically stops and starts the SQL Server Agent service. The user isn't a member of the sysadmin role and isn't a member of any other SQL Server role, but the user can still stop the service. I've denied public access to the xp_servicecontrol procedure, but the change hasn't made a difference. How can I prevent the user from stopping and starting the SQL Server Agent?
This problem is a perfect example of why SQL Server DBAs should have a good knowledge of the underlying OS. Stopping and starting the SQL Server Agent service has nothing to do with SQL Server. The ability to start and stop any service, SQL Server—related or not, is strictly a function of the rights granted to the user's Windows user account. By default, all Windows users who have administrator or power-user permissions can stop, start, and pause services. The Microsoft article "HOW TO: Grant Users Rights to Manage Services in Windows 2000" (http://support.microsoft.com/default.aspx?scid=kb;en-us;q288129) explains how to grant the right to stop and start services. You can't prevent a Windows user who is an administrator or power user from stopping the SQL Server Agent service. The only solutions I might suggest are to remove the rogue user from the Windows administrator group or create a set of policies that clarify who can and can't stop SQL Server—related services.
