获取 SQL 代理服务的服务帐户详细信息
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7324407/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Get Service Account Details of the SQL Agent Service
提问by John
How can I get the Service Account name for the SQL Agent service for a particular SQL Server (SQL 2005). Is it possible to get using SQL statements or WMI ?
如何获取特定 SQL Server (SQL 2005) 的 SQL 代理服务的服务帐户名称。是否可以使用 SQL 语句或 WMI？
采纳答案by lp larsson
回答by KyleMit
As Aaron Bertrand pointed out, you can use the undocumented xp_regreadin SQL Server 2005 and SQL Server 2008, but there is a better way, starting with SQL Server 2008R2 SP1.
正如 Aaron Bertrand 指出的那样，您可以使用xp_regreadSQL Server 2005 和 SQL Server 2008 中未记录的内容，但有更好的方法，从 SQL Server 2008R2 SP1 开始。
From the article How to identify the SQL Server Service Account in T-SQL, you can use sys.dm_server_serviceslike this:
从文章How to identify the SQL Server Service Account in T-SQL，你可以这样使用sys.dm_server_services：
SELECT DSS.servicename,
DSS.startup_type_desc,
DSS.status_desc,
DSS.last_startup_time,
DSS.service_account,
DSS.is_clustered,
DSS.cluster_nodename,
DSS.filename,
DSS.startup_type,
DSS.status,
DSS.process_id
FROM sys.dm_server_services AS DSS;
回答by Conrad Frix
Since SQL Server runs as a windows service you can use wmicto query the start name.
由于 SQL Server 作为 Windows 服务运行，因此您可以使用wmic查询起始名称。
wmic service where "name Like 'MSSQL%'" get Name , StartName
For me this outputs the following (since I've got multiple instances thoes are included as well)
对我来说，这会输出以下内容（因为我有多个实例也包括在内）
Name StartName
MSSQL$SQLEXPRESS NT AUTHORITY\NetworkService
MSSQL$SQLEXPRESS2005 NT AUTHORITY\NetworkService
MSSQLFDLauncher NT AUTHORITY\NETWORK SERVICE
MSSQLSERVER NT AUTHORITY\NETWORK SERVICE
MSSQLServerADHelper NT AUTHORITY\NetworkService
MSSQLServerADHelper100 NT AUTHORITY\NETWORK SERVICE
MSSQLServerOLAPService NT AUTHORITY\NETWORK SERVICE
You can add /NODEto query remote computers. As with any WMI query you will need sufficient privileges in order for this to work
您可以添加/NODE查询远程计算机。与任何 WMI 查询一样，您需要足够的权限才能使其工作
Or the same query using Powershell's Get-WmiObject(Supports remote/multiple computersnames):
或者使用Powershell 的 Get-WmiObject（支持远程/多台计算机名）进行相同的查询：
Get-WmiObject Win32_Service -ComputerName localhost,W-Remote -Filter "name Like 'MSSQL%'" | ft __Server,State,Name,DisplayName,StartName -AutoSize
Sample Output:
示例输出：
__SERVER State Name DisplayName StartName
-------- ----- ---- ----------- ---------
W0123456 Stopped MSSQL$SQLEXPRESS SQL Server (SQLEXPRESS) NT AUTHORITY\NETWORK SERVICE
W0123456 Running MSSQLSERVER SQL Server (MSSQLSERVER) LocalSystem
W0123456 Stopped MSSQLServerADHelper100 SQL Active Directory Helper Service NT AUTHORITY\NETWORKSERVICE
W-REMOTE Stopped MSSQL$SQLEXPRESS SQL Server (SQLEXPRESS) NT AUTHORITY\NETWORK SERVICE
W-REMOTE Running MSSQLSERVER SQL Server (MSSQLSERVER) LocalSystem
回答by Aaron Bertrand
For a default instance:
对于默认实例：
DECLARE @sn NVARCHAR(128);
EXEC master.dbo.xp_regread
'HKEY_LOCAL_MACHINE',
'SYSTEM\CurrentControlSet\services\SQLSERVERAGENT',
'ObjectName',
@sn OUTPUT;
SELECT @sn;
For a named instance, you'll need the second argument to be:
对于命名实例，您需要将第二个参数设为：
'SYSTEM\CurrentControlSet\services\SQLAGENT$InstanceName',
Now, it may not work because you may not have access to xp_regread, and the location of this property may change from version to version (I only tested 2008, 2008 R2 and Denali - I don't have a 2005 instance handy to check).
现在，它可能无法工作，因为您可能无法访问 xp_regread，并且此属性的位置可能会因版本而异（我只测试了 2008、2008 R2 和 Denali - 我没有方便检查的 2005 实例） .
In any case you are probably better off asking the DBA (as suggested in a comment) or, if you have access to the physical machine, just checking the service account in the Control Panel.
在任何情况下，您最好询问 DBA（如评论中所建议的那样），或者，如果您有权访问物理机，只需检查控制面板中的服务帐户。
回答by Nate S.
I know this is an old thread but here is my solution. This has been tested against SQL Server 2000, 2005, 2008, 2008 R2, 2012, 2014 and 2016.
我知道这是一个旧线程，但这是我的解决方案。这已经针对 SQL Server 2000、2005、2008、2008 R2、2012、2014 和 2016 进行了测试。
if (select CONVERT(INT, (REPLACE(SUBSTRING(convert(nvarchar, SERVERPROPERTY('ProductVersion')), 1, 2), '.', '')))) >10
BEGIN
select distinct(service_account) AS SvcAccount from sys.dm_server_services;
END
ELSE
BEGIN
DECLARE @instanceName varchar(100)
set @instanceName = convert(varchar,SERVERPROPERTY ('InstanceName'))
IF (@instanceName) IS NULL
begin
DECLARE @sn NVARCHAR(128);
EXEC master.dbo.xp_regread
'HKEY_LOCAL_MACHINE',
'SYSTEM\CurrentControlSet\services\SQLSERVERAGENT',
'ObjectName',
@sn OUTPUT ;
SELECT @sn AS SvcAccount;
END
ELSE
BEGIN
DECLARE @SQL varchar (500)
SET @SQL = 'DECLARE @sn NVARCHAR(128); exec master.dbo.xp_regread ''HKEY_LOCAL_MACHINE'', ''SYSTEM\CurrentControlSet\services\SQLAgent$'+@instanceName+''',''ObjectName'', @sn OUTPUT; SELECT @sn AS SvcAccount;'
EXEC (@SQL)
END
END
