7.SQL Servers: Find all SQL Services defualt login ID's
SET NOCOUNT ON
DECLARE @REGISTRYPATH VARCHAR(200)
, @NAMEDINSTANCEIND CHAR(1)
, @INSTANCENAME VARCHAR(128)
, @SQLSERVERSVCACCOUNT VARCHAR(128)
, @SQLAGENTSVCACCOUNT VARCHAR(128)
, @DTCSVCACCOUNT VARCHAR(128)
, @OLAPSTARTUP VARCHAR(128)
, @SQLSERVERSTARTUP VARCHAR(128)
, @SQLAGENTSTARTUP VARCHAR(128)
, @DTCSTARTUP VARCHAR(128)
, @OLAPSVCACCOUNT VARCHAR(128)
, @SSRSSVCACCOUNT VARCHAR(128)
, @SSRSTARTUP VARCHAR(128)
CREATE TABLE #SERVICEACCOUNTS (VALUE VARCHAR(50), DATA VARCHAR(50))
IF @@SERVERNAME IS NULL
OR (CHARINDEX('\',@@SERVERNAME)=0)
SET @NAMEDINSTANCEIND = 'N'
ELSE
BEGIN
SET @NAMEDINSTANCEIND = 'Y'
SET @INSTANCENAME = RIGHT( @@SERVERNAME , LEN(@@SERVERNAME) - CHARINDEX('\',@@SERVERNAME))
END
-- SQL SERVER
SET @REGISTRYPATH = 'SYSTEM\CURRENTCONTROLSET\SERVICES\'
IF @NAMEDINSTANCEIND = 'N'
SET @REGISTRYPATH = @REGISTRYPATH + 'MSSQLSERVER'
ELSE
SET @REGISTRYPATH = @REGISTRYPATH + 'MSSQL$' + @INSTANCENAME
INSERT #SERVICEACCOUNTS
EXEC MASTER..XP_REGREAD 'HKEY_LOCAL_MACHINE' , @REGISTRYPATH,'OBJECTNAME'
SELECT @SQLSERVERSVCACCOUNT = DATA FROM #SERVICEACCOUNTS
DELETE FROM #SERVICEACCOUNTS
INSERT #SERVICEACCOUNTS
EXEC MASTER..XP_REGREAD 'HKEY_LOCAL_MACHINE' , @REGISTRYPATH,'START'
SELECT @SQLSERVERSTARTUP = DATA FROM #SERVICEACCOUNTS
DELETE FROM #SERVICEACCOUNTS
-- SQL AGENT
SET @REGISTRYPATH = 'SYSTEM\CURRENTCONTROLSET\SERVICES\'
IF @NAMEDINSTANCEIND = 'N'
SET @REGISTRYPATH = @REGISTRYPATH + 'SQLSERVERAGENT'
ELSE
SET @REGISTRYPATH = @REGISTRYPATH + 'SQLAGENT$' + @INSTANCENAME
INSERT #SERVICEACCOUNTS
EXEC MASTER..XP_REGREAD 'HKEY_LOCAL_MACHINE' , @REGISTRYPATH,'OBJECTNAME'
SELECT @SQLAGENTSVCACCOUNT = DATA FROM #SERVICEACCOUNTS
DELETE FROM #SERVICEACCOUNTS
INSERT #SERVICEACCOUNTS
EXEC MASTER..XP_REGREAD 'HKEY_LOCAL_MACHINE' , @REGISTRYPATH,'START'
SELECT @SQLAGENTSTARTUP = DATA FROM #SERVICEACCOUNTS
DELETE FROM #SERVICEACCOUNTS
-- SSIS
SET @REGISTRYPATH = 'SYSTEM\CURRENTCONTROLSET\SERVICES\MSDTSserver100' --Change to MSDTSserver90 for SQL2005 and MSDTC for SQL2000
INSERT #SERVICEACCOUNTS
EXEC MASTER..XP_REGREAD 'HKEY_LOCAL_MACHINE' , @REGISTRYPATH,'OBJECTNAME'
SELECT @DTCSVCACCOUNT = DATA FROM #SERVICEACCOUNTS
DELETE FROM #SERVICEACCOUNTS
INSERT #SERVICEACCOUNTS
EXEC MASTER..XP_REGREAD 'HKEY_LOCAL_MACHINE' , @REGISTRYPATH,'START'
SELECT @DTCSTARTUP = DATA FROM #SERVICEACCOUNTS
DELETE FROM #SERVICEACCOUNTS
--SSAS
SET @REGISTRYPATH = 'SYSTEM\CURRENTCONTROLSET\SERVICES\MSSQLSERVEROLAPSERVICE'
INSERT #SERVICEACCOUNTS
EXEC MASTER..XP_REGREAD 'HKEY_LOCAL_MACHINE' , @REGISTRYPATH,'OBJECTNAME'
SELECT @OLAPSVCACCOUNT = DATA FROM #SERVICEACCOUNTS
DELETE FROM #SERVICEACCOUNTS
INSERT #SERVICEACCOUNTS
EXEC MASTER..XP_REGREAD 'HKEY_LOCAL_MACHINE' , @REGISTRYPATH,'START'
SELECT @OLAPSTARTUP = DATA FROM #SERVICEACCOUNTS
DELETE FROM #SERVICEACCOUNTS
--SSRS
SET @REGISTRYPATH = 'SYSTEM\CURRENTCONTROLSET\SERVICES\REPORTSERVER'
INSERT #SERVICEACCOUNTS
EXEC MASTER..XP_REGREAD 'HKEY_LOCAL_MACHINE' , @REGISTRYPATH,'OBJECTNAME'
SELECT @SSRSSVCACCOUNT = DATA FROM #SERVICEACCOUNTS
DELETE FROM #SERVICEACCOUNTS
INSERT #SERVICEACCOUNTS
EXEC MASTER..XP_REGREAD 'HKEY_LOCAL_MACHINE' , @REGISTRYPATH,'START'
SELECT @SSRSTARTUP = DATA FROM #SERVICEACCOUNTS
DELETE FROM #SERVICEACCOUNTS
SELECT CAST( SERVERPROPERTY ('SERVERNAME') AS NVARCHAR(128)) AS SERVERNAME
, COALESCE ( CAST( SERVERPROPERTY ('INSTANCENAME') AS NVARCHAR(128) ) , 'DEFAULT') AS INSTANCE
, @SQLSERVERSVCACCOUNT AS [SQL SERVER ACCOUNT]
, @SQLAGENTSVCACCOUNT AS [SQL AGENT ACCOUNT]
, @DTCSVCACCOUNT AS [SSIS]
, @OLAPSVCACCOUNT AS [SSAS]
, @SSRSSVCACCOUNT AS [SSRS]
DROP TABLE #SERVICEACCOUNTS
|