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.
Filed under: Blog Tagged: administration, sql server, syndicated, T-SQL 
