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 NumberDECLARE @xp_msver TABLE ([idx] [int] NULL,[c_name] [varchar](100) NULL,[int_val] [float] NULL,[c_val] [varchar](128) NULL)DECLARE @SQLversion SMALLINTINSERT INTO @xp_msverEXEC ('[master]..[xp_msver] ''ProductVersion''');;SELECT @SQLversion = CONVERT(SMALLINT, LEFT([c_val], CHARINDEX('.', [c_val]) - 1))FROM @xp_msverDECLARE @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 nameSET @server = CONVERT(VARCHAR(55), SERVERPROPERTY('ServerName'))SET @reg_key = 'system\currentcontrolset\services\'--name our servicesIF CHARINDEX('\', @server) = 0 --DEFAULT INSTANCE NAME:MSSQLSERVERBEGINSET @SQLservicename = 'MSSQLSERVER'SET @Agentservicename = 'SQLSERVERAGENT'SET @SSRSServiceName = 'ReportServer'SET @SSASServiceName = 'MSSQLServerOLAPService'SET @SQLBrowserServiceName = 'SQLBrowser'SET @SQLWriterServiceName = 'SQLWriter'SET @SQLFullTextServiceName = 'MSSQLFDLauncher'IF @SQLversion = 9SET @SSISServiceName = 'MsDtsServer'ELSE IF @SQLversion = 10SET @SSISServiceName = 'MsDtsServer100'ELSE IF @SQLversion = 11SET @SSISServiceName = 'MsDtsServer110'ELSE IF @SQLversion = 12SET @SSISServiceName = 'MsDtsServer120'ENDELSEBEGIN--set the instance nameSET @instance = RIGHT(@server, LEN(@server) - CHARINDEX('\', @server, 1))SET @SQLservicename = 'MSSQL$' + @instanceSET @Agentservicename = 'SQLAgent$' + @instanceSET @SSRSServiceName = 'ReportServer$' + @instanceSET @SSASServiceName = 'MSOLAP$' + @instanceSET @SQLBrowserServiceName = 'SQLBrowser'SET @SQLWriterServiceName = 'SQLWriter'SET @SQLFullTextServiceName = 'MSSQLFDLauncher$' + @instanceIF @SQLversion = 9SET @SSISServiceName = 'MsDtsServer'ELSE IF @SQLversion = 10SET @SSISServiceName = 'MsDtsServer100'ELSE IF @SQLversion = 11SET @SSISServiceName = 'MsDtsServer110'ELSE IF @SQLversion = 12SET @SSISServiceName = 'MsDtsServer120'END--initalize the keysSET @SQL_reg_key = @reg_key + @SQLservicenameSET @Agent_reg_key = @reg_key + @AgentservicenameSET @SSRS_reg_key = @reg_key + @SSRSServiceNameSET @SSAS_reg_key = @reg_key + @SSASServiceNameSET @SSIS_reg_key = @reg_key + @SSISServiceNameSET @SQLBrowser_reg_key = @reg_key + @SQLBrowserServiceNameSET @SQLWriter_reg_key = @reg_key + @SQLWriterServiceNameSET @SQLFullText_reg_key = @reg_key + @SQLFullTextServiceName--get the SQL accountEXECUTE master..xp_regread 'HKEY_LOCAL_MACHINE',@SQL_reg_key,'ObjectName',@SQLservicename OUTPUT,'no_output'--get the Agent accountEXECUTE 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'BEGINSET @SSRSServiceName = 'No SSRS'ENDELSE IF @SSRSServiceName = 'ReportServer$' + @instanceBEGINSET @SSRSServiceName = 'No SSRS'ENDIF @SSASServiceName = 'MSSQLServerOLAPService'BEGINSET @SSASServiceName = 'No SSAS'ENDELSE IF @SSASServiceName = 'MSOLAP$' + @instanceBEGINSET @SSASServiceName = 'No SSAS'ENDIF @SSISServiceName = 'MsDtsServer'BEGINSET @SSISServiceName = 'No SSIS'ENDELSE IF @SSISServiceName = 'MsDtsServer100'BEGINSET @SSISServiceName = 'No SSIS'ENDELSE IF @SSISServiceName = 'MsDtsServer110'BEGINSET @SSISServiceName = 'No SSIS'ENDELSE IF @SSISServiceName = 'MsDtsServer120'BEGINSET @SSISServiceName = 'No SSIS'ENDIF @SQLBrowserServiceName = 'SQLBrowser'BEGINSET @SQLBrowserServiceName = 'No SQL Browser'ENDIF @SQLWriterServiceName = 'SQLWriter'BEGINSET @SQLWriterServiceName = 'No SQL Writer'ENDIF @SQLFullTextServiceName = 'MSSQLFDLauncher'BEGINSET @SQLFullTextServiceName = 'No SQL Full Text'ENDELSE IF @SQLFullTextServiceName = 'MSSQLFDLauncher$' + @instanceBEGINSET @SQLFullTextServiceName = 'No SQL Full Text'ENDINSERT INTO @Result ([ServerName],[SQLServiceName],[AgentServiceName],[SSRSServiceName],[SSASServiceName],[SSISServiceName],[SQLBrowserServiceName],[SQLWriterServiceName],[SQLFullTextServiceName])SELECT @@SERVERNAME,@SQLservicename,@Agentservicename,@SSRSServiceName,@SSASServiceName,@SSISServiceName,@SQLBrowserServiceName,@SQLWriterServiceName,@SQLFullTextServiceName;SELECT *FROM @Result
No comments
Post a Comment