Get SQL Service Accounts

2014-12-12

Recently our Domain team created an exclusive OU for DBA. I’d like to control all the SQL service accounts. But SQL server not only has SQL Engine and SQL Agent accounts but also SSIS/SSAS/SSRS/Full Text, etc.....
How do I know all the accounts in SQL Server? If you have SQL 2008+, you can use a new DMV “sys.dm_server_services”.
If you have SQL 2005, you have to use undocumented “xp_regread” function to read registry records that in Windows Server.
It’s also fit to SQL 2008+…

Credit to: http://simondrichards.wordpress.com/2012/07/05/find-the-sql-server-service-accounts-using-tsql/
If you installed SSRS/SSAS separated from SQL Engine, the script won’t work. You have to use PowerShell to get it..I won’t include the part for you
Below is the code:


DECLARE @reg_key VARCHAR(75),
  @instance VARCHAR(16),
  @server VARCHAR(55),
  @SQL_reg_key VARCHAR(150),
  @Agent_reg_key VARCHAR(150),
  @SSRS_reg_key VARCHAR(150),
  @SSAS_reg_key VARCHAR(150),
  @SSIS_reg_key VARCHAR(150),
  @SQLBrowser_reg_key VARCHAR(150),
  @SQLWriter_reg_key VARCHAR(150),
  @SQLFullText_reg_key VARCHAR(150),
  @SQLServiceName VARCHAR(50),
  @AgentServiceName VARCHAR(50),
  @SSRSServiceName VARCHAR(50),
  @SSASServiceName VARCHAR(50),
  @SSISServiceName VARCHAR(50),
  @SQLBrowserServiceName VARCHAR(50),
  @SQLWriterServiceName VARCHAR(50),
  @SQLFullTextServiceName VARCHAR(50)
--Get SQL version Number
DECLARE @xp_msver TABLE (
  [idx] [int] NULL,
  [c_name] [varchar](100) NULL,
  [int_val] [float] NULL,
  [c_val] [varchar](128) NULL
  )
DECLARE @SQLversion SMALLINT
INSERT INTO @xp_msver
EXEC ('[master]..[xp_msver] ''ProductVersion''');;
SELECT @SQLversion = CONVERT(SMALLINT, LEFT([c_val], CHARINDEX('.', [c_val]) - 1))
FROM @xp_msver
DECLARE @Result TABLE (
  [ServerName] SYSNAME NULL,
  [SQLServiceName] VARCHAR(50) NULL,
  [AgentServiceName] VARCHAR(50) NULL,
  [SSRSServiceName] VARCHAR(50) NULL,
  [SSASServiceName] VARCHAR(50) NULL,
  [SSISServiceName] VARCHAR(50) NULL,
  [SQLBrowserServiceName] VARCHAR(50) NULL,
  [SQLWriterServiceName] VARCHAR(50) NULL,
  [SQLFullTextServiceName] VARCHAR(50) NULL
  )
--initalize our key and server name
SET @server = CONVERT(VARCHAR(55), SERVERPROPERTY('ServerName'))
SET @reg_key = 'system\currentcontrolset\services\'
--name our services
IF CHARINDEX('\', @server) = 0 --DEFAULT INSTANCE NAME:MSSQLSERVER
BEGIN
  SET @SQLservicename = 'MSSQLSERVER'
  SET @Agentservicename = 'SQLSERVERAGENT'
  SET @SSRSServiceName = 'ReportServer'
  SET @SSASServiceName = 'MSSQLServerOLAPService'
  SET @SQLBrowserServiceName = 'SQLBrowser'
  SET @SQLWriterServiceName = 'SQLWriter'
  SET @SQLFullTextServiceName = 'MSSQLFDLauncher'
  IF @SQLversion = 9
    SET @SSISServiceName = 'MsDtsServer'
  ELSE IF @SQLversion = 10
    SET @SSISServiceName = 'MsDtsServer100'
  ELSE IF @SQLversion = 11
    SET @SSISServiceName = 'MsDtsServer110'
  ELSE IF @SQLversion = 12
    SET @SSISServiceName = 'MsDtsServer120'
END
ELSE
BEGIN
  --set the instance name
  SET @instance = RIGHT(@server, LEN(@server) - CHARINDEX('\', @server, 1))
  SET @SQLservicename = 'MSSQL$' + @instance
  SET @Agentservicename = 'SQLAgent$' + @instance
  SET @SSRSServiceName = 'ReportServer$' + @instance
  SET @SSASServiceName = 'MSOLAP$' + @instance
  SET @SQLBrowserServiceName = 'SQLBrowser'
  SET @SQLWriterServiceName = 'SQLWriter'
  SET @SQLFullTextServiceName = 'MSSQLFDLauncher$' + @instance
  IF @SQLversion = 9
    SET @SSISServiceName = 'MsDtsServer'
  ELSE IF @SQLversion = 10
    SET @SSISServiceName = 'MsDtsServer100'
  ELSE IF @SQLversion = 11
    SET @SSISServiceName = 'MsDtsServer110'
  ELSE IF @SQLversion = 12
    SET @SSISServiceName = 'MsDtsServer120'
END
--initalize the keys
SET @SQL_reg_key = @reg_key + @SQLservicename
SET @Agent_reg_key = @reg_key + @Agentservicename
SET @SSRS_reg_key = @reg_key + @SSRSServiceName
SET @SSAS_reg_key = @reg_key + @SSASServiceName
SET @SSIS_reg_key = @reg_key + @SSISServiceName
SET @SQLBrowser_reg_key = @reg_key + @SQLBrowserServiceName
SET @SQLWriter_reg_key = @reg_key + @SQLWriterServiceName
SET @SQLFullText_reg_key = @reg_key + @SQLFullTextServiceName
--get the SQL account
EXECUTE master..xp_regread 'HKEY_LOCAL_MACHINE',
  @SQL_reg_key,
  'ObjectName',
  @SQLservicename OUTPUT,
  'no_output'
--get the Agent account
EXECUTE master..xp_regread 'HKEY_LOCAL_MACHINE',
  @Agent_reg_key,
  'ObjectName',
  @Agentservicename OUTPUT,
  'no_output'
EXECUTE master..xp_regread 'HKEY_LOCAL_MACHINE',
  @SSRS_reg_key,
  'ObjectName',
  @SSRSServiceName OUTPUT,
  'no_output'
EXECUTE master..xp_regread 'HKEY_LOCAL_MACHINE',
  @SSAS_reg_key,
  'ObjectName',
  @SSASServiceName OUTPUT,
  'no_output'
EXECUTE master..xp_regread 'HKEY_LOCAL_MACHINE',
  @SSIS_reg_key,
  'ObjectName',
  @SSISServiceName OUTPUT,
  'no_output'
EXECUTE master..xp_regread 'HKEY_LOCAL_MACHINE',
  @SQLBrowser_reg_key,
  'ObjectName',
  @SQLBrowserServiceName OUTPUT,
  'no_output'
EXECUTE master..xp_regread 'HKEY_LOCAL_MACHINE',
  @SQLWriter_reg_key,
  'ObjectName',
  @SQLWriterServiceName OUTPUT,
  'no_output'
EXECUTE master..xp_regread 'HKEY_LOCAL_MACHINE',
  @SQLFullText_reg_key,
  'ObjectName',
  @SQLFullTextServiceName OUTPUT,
  'no_output'
IF @SSRSServiceName = 'ReportServer'
BEGIN
  SET @SSRSServiceName = 'No SSRS'
END
ELSE IF @SSRSServiceName = 'ReportServer$' + @instance
BEGIN
  SET @SSRSServiceName = 'No SSRS'
END
IF @SSASServiceName = 'MSSQLServerOLAPService'
BEGIN
  SET @SSASServiceName = 'No SSAS'
END
ELSE IF @SSASServiceName = 'MSOLAP$' + @instance
BEGIN
  SET @SSASServiceName = 'No SSAS'
END
IF @SSISServiceName = 'MsDtsServer'
BEGIN
  SET @SSISServiceName = 'No SSIS'
END
ELSE IF @SSISServiceName = 'MsDtsServer100'
BEGIN
  SET @SSISServiceName = 'No SSIS'
END
ELSE IF @SSISServiceName = 'MsDtsServer110'
BEGIN
  SET @SSISServiceName = 'No SSIS'
END
ELSE IF @SSISServiceName = 'MsDtsServer120'
BEGIN
  SET @SSISServiceName = 'No SSIS'
END
IF @SQLBrowserServiceName = 'SQLBrowser'
BEGIN
  SET @SQLBrowserServiceName = 'No SQL Browser'
END
IF @SQLWriterServiceName = 'SQLWriter'
BEGIN
  SET @SQLWriterServiceName = 'No SQL Writer'
END
IF @SQLFullTextServiceName = 'MSSQLFDLauncher'
BEGIN
  SET @SQLFullTextServiceName = 'No SQL Full Text'
END
ELSE IF @SQLFullTextServiceName = 'MSSQLFDLauncher$' + @instance
BEGIN
  SET @SQLFullTextServiceName = 'No SQL Full Text'
END
INSERT INTO @Result (
  [ServerName],
  [SQLServiceName],
  [AgentServiceName],
  [SSRSServiceName],
  [SSASServiceName],
  [SSISServiceName],
  [SQLBrowserServiceName],
  [SQLWriterServiceName],
  [SQLFullTextServiceName]
  )
SELECT @@SERVERNAME,
  @SQLservicename,
  @Agentservicename,
  @SSRSServiceName,
  @SSASServiceName,
  @SSISServiceName,
  @SQLBrowserServiceName,
  @SQLWriterServiceName,
  @SQLFullTextServiceName;
SELECT *
FROM @Result

 
Newer Older