Skip to content

Checking Your Service Account with T-SQL

February 22, 2012

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 these ads

From → Blog

3 Comments
  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.

  2. Rick O permalink

    Thanks Steve. Definitely good to know!

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

Comments are closed.

Follow

Get every new post delivered to your Inbox.

Join 4,640 other followers

%d bloggers like this: