Somehow this slipped by me, but there were some new DMVs added in SQL Server 2008 R2 SP1. I suspect my test machines were mostly SQL Server 2008 or SQL Server 2012, and I hadn’t been paying attention to the changes in SP1.

You can now use T-SQL to check for services information, as well as registry information, without using extended stored procedures or any hacks of xp_cmdshell. There are two new DMVs:

These were not present in the RTM of SQL Server 2008 R2, but after installing SP1, they appear. The KB article for SQL Server 2008 R2 SP1 includes a note that new trace templates for Profiler are included, but I did not see a note about these two DMVs.

So much for not adding features in Service Packs.

In any case, you can query the sys.dm_server_services for service account information. You will get the service name, the startup type, the account, and more.

If you aren’t a Windows administrator on your SQL Server boxes, you should still be able to get information regarding the services from this DMV as long as you have VIEW SERVER STATE permission.

3 thoughts on “Checking Your Service Account with T-SQL

  1. This is great for a DBA to be able to query registry settings and service status directly within SSMS or a SQLCMD prompt without having to Remote Console into the server. However, it also potentially exposes an order of magnitude more information for leverage by a hacker. This is yet another reason to minimize which login accounts have SYSADMIN membership.

Comments are now closed.