Listing 1. upCheckSecurityConfiguration

-- BEGIN CALLOUT A
CREATE PROCEDURE upCheckSecurityConfiguration
AS
SET NOCOUNT ON

DECLARE  @TestResult char(10)
DECLARE	 @ActualSetting varchar(12)
--------------------------------------
-- 1: IS SQL Server Service Acct 
-- "LocalSystem"? If so, FAIL
--------------------------------------
DECLARE @ServiceAccount nvarchar(200)
-- Get Actual Setting
EXEC master..xp_regread 
'HKEY_LOCAL_MACHINE',
'SYSTEM\CurrentControlSet\Services\MSSQLSERVER',
	'ObjectName', @ServiceAccount OUTPUT
-- Evaluate Test Result
IF @ServiceAccount = 'LocalSystem'
  	SET @TestResult = 'FAIL'
ELSE
  	SET @TestResult = 'PASS'
-- Log Test
EXEC upInsertTestCase 
	@@ConfigItem = 'ServiceAccount', 
	@@ExpectedSetting = 'NT_ACCOUNT',
	@@TestResult = @TestResult, 
	@@ActualSetting = @ServiceAccount
-- END CALLOUT A

--------------------------------------
-- 2: Latest service pack? If not, FAIL
--------------------------------------
DECLARE @ProductVersion varchar(64)
-- Get Actual Setting
SELECT @ProductVersion = 
cast(SERVERPROPERTY('ProductVersion') 
as varchar(64))
-- [ set your version here ]
IF @ProductVersion >= '8.00' 
	SET @TestResult = 'PASS'
ELSE
	SET @TestResult = 'FAIL'
EXEC upInsertTestCase 
	@@ConfigItem = 'Version#', 
	@@ExpectedSetting = '8.00',
	@@TestResult = @TestResult, 
	@@ActualSetting = @ProductVersion

--------------------------------------
-- 3: Is Windows Authentication 
-- (DWORD = 1) only login method? 
--------------------------------------
DECLARE @LoginMode int
EXECUTE master..xp_regread  
N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'LoginMode',@LoginMode OUTPUT   
IF @LoginMode = 1 
  	SET @TestResult = 'PASS'
ELSE
  	SET @TestResult = 'FAIL'
EXEC upInsertTestCase  
	@@ConfigItem = 'LoginMode', 
	@@ExpectedSetting = 1,
	@@TestResult = @TestResult, 
	@@ActualSetting = @LoginMode

--------------------------------------
-- 4: Is login auditing = 'full' (=3)?
-- If not, FAIL
--------------------------------------
DECLARE @AuditLevel int
EXECUTE master.dbo.xp_regread 
N'HKEY_LOCAL_MACHINE', 
N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'AuditLevel', 
@AuditLevel OUTPUT
IF @AuditLevel = 3
  	SET @TestResult = 'PASS'
ELSE
  	SET @TestResult = 'FAIL'
EXEC upInsertTestCase 
	@@ConfigItem = 'AuditLevel', 
	@@ExpectedSetting = 3,
	@@TestResult = @TestResult, 
	@@ActualSetting = @AuditLevel

--------------------------------------
-- 5: Is MSDTC disabled (=3)?
-- If not, FAIL
--------------------------------------
DECLARE @MSDTC int
EXECUTE master..xp_instance_regread 
N'HKEY_LOCAL_MACHINE', 
N'SYSTEM\CurrentControlSet\Services\MSDTC', 
N'Start',
@MSDTC OUTPUT
IF @MSDTC = 3
  	SET @TestResult = 'PASS'
ELSE IF @MSDTC is null
	SET @TestResult = 'nap'
ELSE
  	SET @TestResult = 'FAIL'
-- Log Test
EXEC upInsertTestCase 
	@@ConfigItem = 'MSDTC', 
	@@ExpectedSetting = 3,
	@@TestResult = @TestResult, 
	@@ActualSetting = @MSDTC

--------------------------------------
-- 6: Does 'Northwind' database exist?
--  If so, FAIL
--------------------------------------
IF DB_ID('Northwind') IS NULL
	BEGIN
		SET @ActualSetting = 'Does not exist'
  		SET @TestResult = 'PASS'
	END 
ELSE
	BEGIN
		SET @ActualSetting = 'Exists'
  		SET @TestResult = 'FAIL'
	END
EXEC upInsertTestCase 
	@@ConfigItem = 'Northwind', 
	@@ExpectedSetting = 'Does not exist',
	@@TestResult = @TestResult, 
	@@ActualSetting = @ActualSetting

--------------------------------------
-- 7: Does 'Pubs' database exist?
-- If so, FAIL
IF DB_ID('pubs') IS NULL
	BEGIN
		SET @ActualSetting = 'Does not exist'
  		SET @TestResult = 'PASS'
	END 
ELSE
	BEGIN
		SET @ActualSetting = 'Exists'
  		SET @TestResult = 'FAIL'
	END
EXEC upInsertTestCase 
	@@ConfigItem = 'Pubs', 
	@@ExpectedSetting = 'Does not exist',
	@@TestResult = @TestResult, 
	@@ActualSetting = @ActualSetting

--------------------------------------
-- 8: Is remote access disabled (=0)?
-- If not, FAIL
--------------------------------------
IF EXISTS (SELECT value 
FROM master..sysconfigures 
WHERE comment = 
'Allow remote access' and value = 0)
	BEGIN
		SET @ActualSetting = 'Disabled'
  		SET @TestResult = 'PASS'
	END 
ELSE
	BEGIN
		SET @ActualSetting = 'Enabled'
  		SET @TestResult = 'FAIL'
	END
EXEC upInsertTestCase 
	@@ConfigItem = 'Remote access', 
	@@ExpectedSetting = 'Disabled',
	@@TestResult = @TestResult, 
	@@ActualSetting = @ActualSetting

-------------------------------------------------------------------
-- 9: Can system tables be changed (=0)? If so, FAIL
-------------------------------------------------------------------
IF EXISTS (SELECT value FROM master..sysconfigures WHERE comment = 
'Allow updates to system tables' and value = 0)
	BEGIN
		SET @ActualSetting = 'False'
  		SET @TestResult = 'PASS'
	END 
ELSE
	BEGIN
		SET @ActualSetting = 'True'
  		SET @TestResult = 'FAIL'
	END
EXEC upInsertTestCase 
	@@ConfigItem = 'System tables updatable?', 
	@@ExpectedSetting = 'False',
	@@TestResult = @TestResult, 
	@@ActualSetting = @ActualSetting

-------------------------------------------------------------------
-- 10: Are Non-TCP network libraries disabled? If not, FAIL
-------------------------------------------------------------------
CREATE TABLE #NetLib (Item varchar(64), value varchar(32), data varchar(32))
INSERT #NetLib
	EXEC master.dbo.xp_regread 
		N'HKEY_LOCAL_MACHINE',
	 N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib', 
		N'ProtocolList'
If (select value from #NetLib where value <> 'tcp') is not null
	BEGIN
		SET @ActualSetting = 'Enabled'
  		SET @TestResult = 'FAIL' 
	END 
ELSE
	BEGIN
		SET @ActualSetting = 'Disabled'
  		SET @TestResult = 'PASS'
	END
EXEC upInsertTestCase 
	@@ConfigItem = 'Non-TCP Libs', 
	@@ExpectedSetting = 'Disabled',
	@@TestResult = @TestResult, 
	@@ActualSetting = @ActualSetting

-------------------------------------------------------------------
-- 11: Is GUEST login disabled? If not, FAIL
-------------------------------------------------------------------
IF NOT EXISTS (SELECT name FROM sysusers WHERE name = 'guest')
	BEGIN
		SET @ActualSetting = 'Disabled'
  		SET @TestResult = 'PASS' 
	END 
ELSE
	BEGIN
		SET @ActualSetting = 'Enabled'
  		SET @TestResult = 'FAIL'
	END
EXEC upInsertTestCase 
	@@ConfigItem = 'GUEST login', 
	@@ExpectedSetting = 'Disabled',
	@@TestResult = @TestResult, 
	@@ActualSetting = @ActualSetting

-------------------------------------------------------------------
-- 12: Can PUBLIC execute SQL Agent job procedures? If so, FAIL
-------------------------------------------------------------------
IF NOT EXISTS (SELECT * 
		FROM msdb..sysprotects p 
     JOIN msdb..sysusers u on p.uid= u.uid 
     JOIN msdb..sysobjects o on o.id = p.id
		WHERE u.name = 'public' 
     AND action = 224 -- Execute
     AND o.name like '%job%') 
	BEGIN
		SET @ActualSetting = 'False'
  		SET @TestResult = 'PASS' 
	END 
ELSE
	BEGIN
		SET @ActualSetting = 'True'
  		SET @TestResult = 'FAIL'
	END
EXEC upInsertTestCase 
	@@ConfigItem = 'PUBLIC can exec JOB procs', 
	@@ExpectedSetting = 'False',
	@@TestResult = @TestResult, 
	@@ActualSetting = @ActualSetting

-------------------------------------------------------------------
-- 13: Can PUBLIC execute DTS procs? If so, FAIL 
-------------------------------------------------------------------
IF NOT EXISTS (SELECT *  
		FROM msdb..sysprotects p
     JOIN msdb..sysusers u on p.uid = u.uid
     JOIN msdb..sysobjects o
     ON p.uid= u.uid
		WHERE u.name = 'public' 
			AND action = 224
           AND o.name like '%dts%' )
	BEGIN
		SET @ActualSetting = 'False'
  		SET @TestResult = 'PASS' 
	END 
ELSE
	BEGIN
		SET @ActualSetting = 'True'
  		SET @TestResult = 'FAIL'
	END
EXEC upInsertTestCase 
	@@ConfigItem = 'PUBLIC can exec DTS procs', 
	@@ExpectedSetting = 'False',
	@@TestResult = @TestResult, 
	@@ActualSetting = @ActualSetting

-------------------------------------------------------------------
-- 14: Can PUBLIC update/delete mswebtasks table? If so, FAIL
-- (where 195=insert; 196=delete; 197=update) 
-------------------------------------------------------------------
IF NOT EXISTS (SELECT *
		FROM msdb..sysprotects p 
		JOIN msdb..sysobjects o on o.id = p.id
     JOIN msdb..sysusers u on p.uid= u.uid 
		WHERE  u.name = 'public' 
		AND    o.name = 'rtbldbmprops'
		AND    action in (195,196,197))  
	BEGIN
		SET @ActualSetting = 'False'
  		SET @TestResult = 'PASS' 
	END 
ELSE
	BEGIN
		SET @ActualSetting = 'True'
		SET @TestResult = 'FAIL'
	END
EXEC upInsertTestCase 
	@@ConfigItem = 'PUBLIC can access MSWEBTASKS table', 
	@@ExpectedSetting = 'False',
	@@TestResult = @TestResult, 
	@@ActualSetting = @ActualSetting

-------------------------------------------------------------------
-- 15: Can PUBLIC run sensitive procedures?  If so, FAIL. 
-------------------------------------------------------------------
IF NOT EXISTS (SELECT * 
FROM master..sysprotects p 
JOIN master..sysobjects o on o.id = p.id
JOIN master..sysusers u 
ON p.uid= u.uid 
WHERE u.name = 'public'  
and action = 224     
AND o.name in ('sp_runwebtask', 'xp_regread', 'xp_instance_regread '))
	BEGIN
		SET @ActualSetting = 'False'
		SET @TestResult = 'PASS' 
	END 
ELSE
	BEGIN
		SET @ActualSetting = 'True'
		SET @TestResult = 'FAIL'
	END
EXEC upInsertTestCase
	@@ConfigItem = 'PUBLIC can exec sensitive procs', 
	@@ExpectedSetting = 'False',
	@@TestResult = @TestResult, 
	@@ActualSetting = @ActualSetting

RETURN