Checking Your Service Account with T-SQL

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.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , , . Bookmark the permalink.

3 Responses to Checking Your Service Account with T-SQL

  1. Eric Russell says:

    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.

  2. Rick O says:

    Thanks Steve. Definitely good to know!

  3. way0utwest says:

    Good point about security. It’s not just sysadmins, it’s VIEW SERVER STATE permission holders, so be careful there.

Comments are closed.