SQL Server System Audit Report

  • Rudy Panigas

    SSChampion

    Points: 10695

    Comments posted to this topic are about the item SQL Server System Audit Report

    Rudy

  • Kishore.P

    SSCrazy Eights

    Points: 8147

    Hi Rudy,

    Script getting errors in #TrustedDB & #SQL_Server_Settings, these tables were not created and simply using in the query.

    Also observed some place "=" sign missed on where clause as well as in If condition like (IF @InstanceName  'Default Instance')

    Please look into the script and correct.  Also requested to download the script as it is, instead of copy and paste.

    Regards,

    Kishore

  • Rudy Panigas

    SSChampion

    Points: 10695

    Hello Kishore,

    It seems that once the site was converted they first didn't have the code and now it is missing parts if the code. I have attached the  completed code that compiles and runs without errors. This is a newer version 5.9 .

    I will see what I can do with the article. It is hard to get someone here to help with the issues I have been experiencing with my articles. In the meantime here is the code.

    Also, will be posting the newest version soon.

    Thanks

    Rudy

  • mpaivant

    Newbie

    Points: 2

    Bom dia!!

    Can you explain the error?

    Msg 102, Level 15, State 1, Line 416

    Incorrect syntax near '@NodeName1'.

    Msg 102, Level 15, State 1, Line 583

    Incorrect syntax near '1'.

    Msg 156, Level 15, State 1, Line 617

    Incorrect syntax near the keyword 'ELSE'.

    Msg 102, Level 15, State 1, Line 1625

    Incorrect syntax near '3'.

    Msg 156, Level 15, State 1, Line 1657

    Incorrect syntax near the keyword 'ELSE'.

    Msg 102, Level 15, State 1, Line 1701

    Incorrect syntax near '0'.

    Msg 102, Level 15, State 1, Line 1727

    Incorrect syntax near '0'.

    Msg 102, Level 15, State 1, Line 1953

    Incorrect syntax near '1'.

    Msg 102, Level 15, State 1, Line 2118

    Incorrect syntax near 'Default Instance'.

  • Rudy Panigas

    SSChampion

    Points: 10695

    I don't understand why this site is having issues with script. I have copied\pasted the code below. Hope you see it

    /*

    QL Server [A]nalysis and ecurity [A]udit [T]ool (SASAT)

    Created by Rudy Panigas

    SQL Server 2012, 2014, 2016 and 2017

    This script will analyze the SQL Server setting and produce a report on the findings

    The report shows the summary of the server's information, analyzes of possible dangerous setting and

    analyzes the security configuration. If security issues are found, explanation(s) and recommendation(s)

    are show and how to made the change(s) with either SSMS or T-SQL script(s). This script works on SQL 2012 and higher.

    ***********************************************************************************************************************************

    *

    * * Disclaimer ** Use this script at your own risk. The author does not take any responsibilities for correctness of report.

    * ! * All findings should be reviewed with production DBAs, auditors and Microsoft support. **

    * *

    *********

    ************************************************************************************************************************************

    Apr 10, 2015 - Version 1.0 - 1.9 -Initial build

    Apr 16, 2015 - Version 2.0 - 2.3 -Testing and correcting errors

    Apr 20, 2015 - Version 2.4 -Added server inventory output and audit analysis

    Apr 20, 2015 - Version 2.5 -Added remediation scripts

    Apr 21, 2015 - Version 2.6 -Added totals and percentage passed

    Apr 21, 2015 - Version 2.7 -Removed testing section

    May 15, 2015 - Version 2.8 -Added check for xp_fixeddrives and Trace Flag detection

    May 28, 2015 - Version 2.9 -Change some wording and web link verification

    May 29, 2015 - Version 3.0 -Tweaked the output of results. Change name to SASAT (Server Analysis and Security Audit Tool)

    Jun 01, 2015 - Version 3.1 -Change incorrect logic with default trace file detection.

    -Tested the script on SQL 2005, 2008, 2008R2, 2012 and 2014 with no issues

    -Added audit for login authentication

    Jun 12, 2015 - Version 3.2 -Removed the need to change the sp_configure 'show advanced options'

    -Added audit for 'sa' account

    Jun 15, 2015 - Version 3.3 -Changed output format for better viewing

    Aug 20, 2015 - Version 3.4 -Changed output results format for better viewing. Tested all HTTP links

    Aug 21, 2015 - Version 3.5 -Added detection of SysAdmin Members and ServerAdmin Members

    Aug 24, 2015 - Version 3.6 -Changes to output of numbered steps. Added display for sp_configure with effect of changes

    Aug 27, 2015 - Version 3.7 -Changed output format for better viewing

    Sep 02, 2015 - Version 3.8 -Added detection of Instance and provide steps for manual analysis

    Sep 03, 2015 - Version 3.9 -Removed the need to display of sp_configure at end of report. Code commented out

    -Changed output for sys and Server Admin display

    Sep 04, 2015 - Version 4.0 -Added detection of SQL installation on physical or virtual server

    Sep 15, 2015 - Version 4.1 -Correct port number detection for NULL value. Added IP detection

    Sep 15, 2015 - Version 4.2 -Changed detection of Name Pipe or TCP connection

    Sep 16, 2015 - Version 4.3 -Updated URL links

    Sep 17, 2015 - Version 4.4 -Changed output for Trace Flag information. Fixed detection of xp_cmdshell

    Sep 21, 2015 - Version 4.5 -Changed output for better reading of results

    Oct 13, 2015 - Version 4.6 -Changed output for better reading of results, again

    Oct 28, 2015 - Version 4.7 -Added display of total memory of server

    Jan 06, 2016 - Version 4.8 -Added detection of Trustworthy setting on databases

    Jan 08, 2016 - Version 4.9 -Provide list of section that have failed the analyzes/audit

    Jan 22, 2016 - Version 5.0 -Added additional checks which brings the total checks to 40

    -set Auto_Close Off on contained databases

    -Revoke CONNECT permissions on the Guest user

    -Drop Orphaned User from databases

    -SQL Authentication in contained databases

    -Set the 'CHECK_EXPIRATION' Option to ON for All SQL Authenticated Logins Within the Sysadmin Role

    -Set the 'CHECK_POLICY' Option to ON for All SQL Authenticated Logins

    -Set the 'CLR Assembly Permission Set' to SAFE_ACCESS for All CLR Assemblies

    -Updated links to point to Microsoft sites for SQL 2014 and 2012. All links working as of this date

    -Added additional comments on hidden instances

    -Provide list of stored procedure that can auto execute on SQL Server start up

    Jan 27, 2016 - Version 5.1 -Cleaned up some more of the output

    -Change the detection of version numbers for SQL 2016 and 2018 ** Not fully confirmed from MS as only 2016 CTP is out

    Feb 17, 2016 - Version 5.2 -Add reference for Database Mail. Corrected grimmer

    Oct 03, 2016 - Version 5.3 -Correct insert error that effects some systems

    Feb 07, 2017 - Version 5.4 -Changes to output for better cut and paste into report

    Aug 09, 2017 - Version 5.5 -Added script that will change the audit settings to both Failed/Successful logins

    Aug 30, 2017 - Version 5.6 -Tested the script on SQL 2016 with no issues

    Feb 01, 2018 - Version 5.7 -Changes to correctly detect SQL Server 2017

    Mar 19, 2018 - Version 5.8 -Testing on SQL 2017 passed

    Apr 05, 2018 - Version 5.9 -Additional checks which brings the total checks to 43

    -Datbaase CHECKSUMS

    -Database AUTO_SHRINK

    -Recent databases backups

    ----------------------- Version Control -------------------------------*/

    SET NOCOUNT ON;

    USE MASTER;

    DECLARE @UpdatedDate VARCHAR(30)

    SET @UpdatedDate = 'Apr 05, 2018 - Version 5.9'

    -- Pre Cleanup, if temp tables exist

    IF OBJECT_ID('tempdb..#AutoStart') IS NOT NULL

    BEGIN

    DROP TABLE #AutoStart;

    END

    IF OBJECT_ID('tempdb..#OrphanUserLIst') IS NOT NULL

    BEGIN

    DROP TABLE #OrphanUserLIst;

    END

    IF OBJECT_ID('tempdb..#SQLAuthCD') IS NOT NULL

    BEGIN

    DROP TABLE #SQLAuthCD;

    END

    IF OBJECT_ID('tempdb..#CLRAssemblyPermission') IS NOT NULL

    BEGIN

    DROP TABLE #CLRAssemblyPermission;

    END

    IF OBJECT_ID('tempdb..#CHECK_POLICY') IS NOT NULL

    BEGIN

    DROP TABLE #CHECK_POLICY;

    END

    IF OBJECT_ID('tempdb..#CHECK_EXPIRATION') IS NOT NULL

    BEGIN

    DROP TABLE #CHECK_EXPIRATION;

    END

    IF OBJECT_ID('tempdb..#CONNECTRevokeGuest') IS NOT NULL

    BEGIN

    DROP TABLE #CONNECTRevokeGuest;

    END

    IF OBJECT_ID('tempdb..#OrphanUers') IS NOT NULL

    BEGIN

    DROP TABLE #OrphanUers;

    END

    IF OBJECT_ID('tempdb..#TrustedDB') IS NOT NULL

    BEGIN

    DROP TABLE #TrustedDB;

    END

    IF OBJECT_ID('tempdb..#nodes') IS NOT NULL

    BEGIN

    DROP TABLE #nodes;

    END

    IF OBJECT_ID('tempdb..#KERBINFO') IS NOT NULL

    BEGIN

    DROP TABLE #KERBINFO;

    END

    IF OBJECT_ID('tempdb..#SysAdminAccount') IS NOT NULL

    BEGIN

    DROP TABLE #SysAdminAccount;

    END

    IF OBJECT_ID('tempdb..#SrvAdmin') IS NOT NULL

    BEGIN

    DROP TABLE #SrvAdmin;

    END

    IF OBJECT_ID('tempdb..#SQL_Server_Settings') IS NOT NULL

    BEGIN

    DROP TABLE #SQL_Server_Settings;

    END

    IF OBJECT_ID('tempdb..#TraceStats') IS NOT NULL

    BEGIN

    DROP TABLE #TraceStats;

    END

    IF OBJECT_ID('tempdb..#SASATFailed') IS NOT NULL

    BEGIN

    DROP TABLE #SASATFailed;

    END

    -------------------------------------------------------------------------

    PRINT ''

    PRINT ' SQL Server Analysis and Security Audit Tool (SASAT) '

    PRINT ' ***************************************************'

    PRINT ' Created by Rudy Panigas. Updated on '+@UpdatedDate

    PRINT ''

    PRINT 'This script will analyze/audit your SQL Server settings and report on the findings. The report shows the summary of SQL server''s information,'

    PRINT 'analyze possible dangerous settings and analyzes the security configuration. If issues are detected then explanations, recommendations and how'

    PRINT 'to make changes (either with SSMS or with T-SQL scripts) are shown.'

    PRINT ''

    PRINT ' The following (43) SQL Server settings are reviewed (SQL Server 2012 and higher versions)'

    PRINT ''

    PRINT ' TRUSTWORTHY Databases Allow Remote Access Cross DB Ownership Chaining Max Worker Threads'

    PRINT ' Priority Boost Lightweight Pooling Startup Stored Procedures Affinity64 Mask'

    PRINT ' Affinity I/O Mask Affinity64 I/O Mask CLR enabled Database Mail XPs'

    PRINT ' OLE Automation Procedures Ad Hoc Distributed Queries sa account Remote Admin Connections'

    PRINT ' Default Trace file Default SQL Port Number xp_dirtree xp_fixeddrives'

    PRINT ' xp_enumgroups xp_servicecontrol xp_subdirs xp_regaddmultistring'

    PRINT ' xp_regdeletekey xp_regdeletevalue xp_regenumvalues xp_regremovemultistring'

    PRINT ' xp_regwrite xp_regwrite Audit Level xp_cmdshell'

    PRINT ' Server Authentication Auto_Close Orphaned Users CLR Assembly Permission'

    PRINT ' CONNECT Permissions for Guest CHECK_POLICY SQL Authenticated CHECK_EXPIRATION SQL Authentication (Contained databases)'

    PRINT ' Auto_Shrink Database Checksum Recent Backups'

    PRINT ''

    DECLARE

    @CurrentDate NVARCHAR(12) -- Current data/time

    ,@SQLServerName NVARCHAR(50) --Set SQL Server Name

    ,@NodeName1 NVARCHAR(50) -- Name of node 1 if clustered

    ,@NodeName2 NVARCHAR(50) -- Name of node 2 if clustered

    ,@NodeName3 NVARCHAR(50) /* -- remove remarks if more than 2 node cluster */

    ,@NodeName4 NVARCHAR(50) /*-- remove remarks if more than 2 node cluster */

    ,@AccountName NVARCHAR(50) -- Account name used

    ,@StaticPortNumber NVARCHAR(50) -- Static port number

    ,@INSTANCENAME NVARCHAR(30) -- SQL Server Instance Name

    ,@VALUENAME NVARCHAR(20) -- Detect account used in SQL 2005, see notes below

    ,@KERB NVARCHAR(50) -- Is Kerberos used or not

    ,@DomainName NVARCHAR(50) -- Name of Domain

    ,@IP NVARCHAR(20) -- IP address used by SQL Server

    ,@InstallDate NVARCHAR(20) -- Installation date of SQL Server

    ,@ProductVersion NVARCHAR(30) -- Production version

    ,@MachineName NVARCHAR(30) -- Server name

    ,@ServerName NVARCHAR(30) -- SQL Server name

    ,@EDITION NVARCHAR(30) --SQL Server Edition

    ,@ProductLevel NVARCHAR(20) -- Product level

    ,@ISClustered NVARCHAR(20) -- System clustered

    ,@ISIntegratedSecurityOnly NVARCHAR(50) -- Security level

    ,@ISSingleUser NVARCHAR(20) -- System in Single User mode

    ,@COLLATION NVARCHAR(30) -- Collation type

    ,@physical_CPU_Count VARCHAR(4) -- CPU count

    ,@EnvironmentType VARCHAR(15) -- Physical or Virtual

    ,@MachineType INT -- Server type

    ,@MaxMemory NVARCHAR(10) -- Max memory

    ,@MinMemory NVARCHAR(10) -- Min memory

    ,@TotalMEMORYinBytes NVARCHAR(10) -- Total memory

    ,@TotalMEMORYinMegaBytes NVARCHAR(10) -- Converted value of physical server memory in megabytes

    ,@ErrorLogLocation VARCHAR(500) -- location of error logs

    ,@TraceFileLocation VARCHAR(100) -- location of trace files

    ,@LinkServers VARCHAR(2) -- Number of linked servers found

    ,@FileStreams VARCHAR(2) -- Is FileStreams enabled

    ,@BackUpCompression VARCHAR(2) -- Is backup compression enabled

    ,@TestResultCounter NUMERIC (3,0) -- tracks total tests passed and is used in final reporting section

    ,@ResultsPercentage NUMERIC (3,0) -- Results as percentage passed

    ,@TotalAutomatedTests NUMERIC (3,0) --Total automated test

    ,@DefaultTraceEnabled VARCHAR(2) -- Is default trace enabled

    ,@xp_cmdshellEnabled VARCHAR(2) -- Is command shell enabled

    ,@RemoteAdminConnections VARCHAR(2) -- is remote admin connection enabled

    ,@xp_dirtreeEnabled NVARCHAR(10) -- is xp_dirtree enabled

    ,@xp_fixeddrivesEnabled NVARCHAR(10) -- is xp_emumgroups enabled

    ,@xp_enumgroupsEnabled NVARCHAR(10) -- is xp_emumgroups enabled

    ,@xp_servicecontrolEnabled NVARCHAR(10) -- is xp_servicecontrol enabled

    ,@xp_subdirsEnabled NVARCHAR(10) -- is xp_subdirs enabled

    ,@xp_regaddmultistringEnabled NVARCHAR(10) -- is xp_readdmultistring enabled

    ,@xp_regdeletekeyEnabled NVARCHAR(10) -- is xp_regdeletekey enabled

    ,@xp_regdeletevalueEnabled NVARCHAR(10) -- is xp_regdeletevalue enabled

    ,@xp_regenumvaluesEnabled NVARCHAR(10) -- is xp_regnumvalues enabled

    ,@xp_regremovemultistringEnabled NVARCHAR(10) -- is xp_regremovemultistring enabled

    ,@xp_regwriteEnabled NVARCHAR(10) -- is xp_regwrite enabled

    ,@xp_regreadEnabled NVARCHAR(10) -- is xp_regread enabled

    ,@SADisabled NVARCHAR(15) -- is the 'sa' account enabled

    ,@TRANSPORT NVARCHAR(20) -- Connection type

    ,@AuditLevel int -- Connection audit levels

    ,@AuditLvltxt VARCHAR(50) -- Connection audit levels description

    SET @TestResultCounter = 0 -- setting result counter to zero

    SET @ResultsPercentage = 0 -- setting percentage counter to zero

    SET @TotalAutomatedTests = 43 -- setting total number of automated tests

    SET @CurrentDate = (SELECT GETDATE())

    SET @ServerName = (SELECT @@SERVERNAME)

    CREATE TABLE #SASATFailed -- Record sections that have failed audit

    (AuditName NVARCHAR(100));

    PRINT 'Report generated for '''+@ServerName+''' SQL Server on '+@CurrentDate

    PRINT ''

    PRINT '******** SQL Server Summary ********'

    PRINT ''

    SET @SQLServerName = (SELECT @@ServerName)

    PRINT '* Detected - SQL Server name\Instance name --> '+@SQLServerName

    SET @InstallDate = (SELECT createdate FROM sys.syslogins where sid = 0x010100000000000512000000)

    PRINT '* Detected - Installation Date --> '+@InstallDate

    SET @MachineName = (SELECT CONVERT(char(100), SERVERPROPERTY('MachineName')))

    PRINT '* Detected - Machine Name --> '+@MachineName

    SET @InstanceName = (SELECT CONVERT(char(50), SERVERPROPERTY('InstanceName')))

    IF (@InstanceName IS NULL) SET @InstanceName = 'Default Instance'

    PRINT '* Detected - Instance Name --> '+@InstanceName

    SET @EDITION = (SELECT CONVERT(char(30), SERVERPROPERTY('EDITION')))

    PRINT '* Detected - Edition and BIT Level --> '+@EDITION

    SET @ProductLevel = (SELECT CONVERT(char(30), SERVERPROPERTY('ProductLevel')))

    PRINT '* Detected - Production Service Pack Level --> '+@ProductLevel

    SET @ProductVersion = (SELECT CONVERT(char(30), SERVERPROPERTY('ProductVersion')))

    PRINT '* Detected - Production Version --> '+@ProductVersion

    IF @ProductVersion LIKE '6.5%'

    BEGIN

    SET @ProductVersion = 'SQL Server 6.5'

    SET @MachineType = 6

    END

    IF @ProductVersion LIKE '7.0%'

    BEGIN

    SET @ProductVersion = 'SQL Server 7'

    SET @MachineType = 7

    END

    IF @ProductVersion LIKE '8.0%'

    BEGIN

    SET @ProductVersion = 'SQL Server 2000'

    SET @MachineType = 8

    END

    IF @ProductVersion LIKE '9.0%'

    BEGIN

    SET @ProductVersion = 'SQL Server 2005'

    SET @MachineType = 9

    END

    IF @ProductVersion LIKE '10.0%'

    BEGIN

    SET @ProductVersion = 'SQL Server 2008'

    SET @MachineType = 10

    END

    IF @ProductVersion LIKE '10.50%'

    BEGIN

    SET @ProductVersion = 'SQL Server 2008R2'

    SET @MachineType = 10

    END

    IF @ProductVersion LIKE '11.0%'

    BEGIN

    SET @ProductVersion = 'SQL Server 2012'

    SET @MachineType = 11

    END

    IF @ProductVersion LIKE '12.0%'

    BEGIN

    SET @ProductVersion = 'SQL Server 2014'

    SET @MachineType = 12

    END

    IF @ProductVersion LIKE '13.0%'

    BEGIN

    SET @ProductVersion = 'SQL Server 2016'

    SET @MachineType = 13

    END

    IF @ProductVersion LIKE '14.0%'

    BEGIN

    SET @ProductVersion = 'SQL Server 2017'

    SET @MachineType = 14

    END

    IF @ProductVersion LIKE '15.0%'

    BEGIN

    SET @ProductVersion = 'SQL Server 2018'

    SET @MachineType = 15 -- for future use

    END

    IF @ProductVersion LIKE '16.0%'

    BEGIN

    SET @ProductVersion = 'SQL Server 2020'

    SET @MachineType = 15 -- for future use

    END

    PRINT '* Detected - Production Name --> '+@ProductVersion

    IF @MachineType >= 11

    BEGIN

    IF(SELECT virtual_machine_type FROM sys.dm_os_sys_info) = 1

    SET @EnvironmentType = 'Virtual'

    IF(SELECT virtual_machine_type FROM sys.dm_os_sys_info) = 0

    SET @EnvironmentType = 'Physical'

    PRINT '* Detected - Environment Type --> '+@EnvironmentType

    END

    SET @physical_CPU_Count = (SELECT cpu_count FROM sys.dm_os_sys_info)

    PRINT '* Detected - Logical CPU Count --> '+@physical_CPU_Count

    SET @TotalMEMORYinBytes = CONVERT(NVARCHAR(10),(select physical_memory_kb from sys.dm_os_sys_info))

    SET @TotalMEMORYinMegaBytes = (@TotalMEMORYinBytes /(1024))

    PRINT '* Detected - Total Memory (Megabytes) --> '+@TotalMEMORYinMegaBytes

    SET @MaxMemory = CONVERT(NVARCHAR(10), (SELECT VALUE FROM SYS.CONFIGURATIONS where Name like 'max server memory%'))

    SET @MinMemory = CONVERT(NVARCHAR(10), (SELECT VALUE FROM SYS.CONFIGURATIONS where Name like 'min server memory%'))

    PRINT '* Detected - Maximum Memory (Megabytes) --> '+@MaxMemory

    PRINT '* Detected - Minimum Memory (Megabytes) --> '+@MinMemory

    SET @IP = (SELECT DEC.Local_Net_Address FROM sys.dm_exec_connections AS DEC WHERE DEC.session_id = @@SPID)

    IF (@IP IS NULL)

    BEGIN

    PRINT '* Detected - IP Address --> No connection with IP address made'

    END

    ELSE

    BEGIN

    PRINT '* Detected - IP Address --> '+@IP

    SET @StaticPortNumber = (SELECT local_tcp_port FROM sys.dm_exec_connections WHERE session_id = @@SPID)

    PRINT '* Detected - Port Number --> '+@StaticPortNumber

    END

    SET @DomainName = (SELECT DEFAULT_DOMAIN())

    PRINT '* Detected - Default Domain Name --> '+@DomainName

    -------------------------------------------------------------------------------------------------------------------------

    --For Service Account Name - This line will work on SQL 2008R2 and higher only

    SET @AccountName = (SELECT top 1 service_account FROM sys.dm_server_services)

    EXECUTE master.dbo.xp_instance_regread

    @rootkey = N'HKEY_LOCAL_MACHINE',

    @key = N'SYSTEM\CurrentControlSet\Services\MSSQLServer',

    @value_name = N'ObjectName',

    @value = @AccountName OUTPUT

    -- -- Use this section, instead of the above if your are scanning SQL Servers 2008 and lower

    --EXECUTE master.dbo.xp_instance_regread

    -- @rootkey = N'HKEY_LOCAL_MACHINE',

    -- @key = N'SYSTEM\CurrentControlSet\Services\MSSQLServer',

    -- @value_name = N'ObjectName',

    -- @value = @AccountName OUTPUT

    -------------------------------------------------------------------------------------------------------------------------

    PRINT '* Detected - Service Account name --> '+@AccountName

    IF (SELECT CONVERT(char(30), SERVERPROPERTY('ISClustered'))) = 1

    SET @ISClustered = 'Clustered'

    ELSE

    SET @ISClustered = 'Not Clustered'

    PRINT '* Detected - Clustered Status --> '+@ISClustered

    --cluster node names. Modify if there are more than 2 nodes in cluster

    SELECT NodeName INTO #nodes FROM sys.dm_os_cluster_nodes

    IF @@rowcount = 0

    BEGIN

    SET @NodeName1 = 'NONE' -- NONE for no cluster

    END

    ELSE

    BEGIN

    SET @NodeName1 = (SELECT top 1 NodeName from #nodes)

    SET @NodeName2 = (SELECT NodeName from #nodes where NodeName <> @NodeName1)

    -- Add code here if more that 2 node cluster

    --SET @NodeName3 = (SELECT NodeName from #nodes where NodeName <> @NodeName1 AND NodeName <> @NodeName2)

    --SET @NodeName4 = (SELECT NodeName from #nodes where NodeName <> @NodeName1 AND NodeName <> @NodeName2 AND NodeName <> @NodeName3)

    END

    IF @NodeName1 = 'NONE'

    BEGIN

    PRINT '* Detected - Cluster --> SQL Server is not clustered'

    END

    ELSE

    BEGIN

    PRINT '* Detected - cluster node 1 --> '+@NodeName1

    PRINT '* Detected - cluster node 2 --> '+@NodeName2

    -- --Add code here if more that 2 node cluster

    --PRINT '* Detected - cluster node 3 --> '+@NodeName3

    --PRINT '* Detected - cluster node 4 --> '+@NodeName4

    END

    SELECT net_transport, auth_scheme INTO #KERBINFO FROM sys.dm_exec_connections WHERE session_id = @@spid

    IF @@rowcount = 0

    BEGIN

    SET @KERB = 'Kerberos not used in TCP network transport'

    END

    ELSE

    BEGIN

    SET @KERB = 'TCP is using Kerberos'

    END

    PRINT '* Detected - Kerberos --> '+@KERB

    IF (SELECT CONVERT(char(30), SERVERPROPERTY('ISIntegratedSecurityOnly'))) = 1

    SET @ISIntegratedSecurityOnly = 'Windows Authentication Security Mode'

    ELSE

    SET @ISIntegratedSecurityOnly = 'SQL Authentication and Windows Authentication Mode '

    PRINT '* Detected - Security Mode --> '+@ISIntegratedSecurityOnly

    EXEC MASTER.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',

    N'Software\Microsoft\MSSQLServer\MSSQLServer', N'AuditLevel', @AuditLevel OUTPUT

    SELECT @AuditLvltxt = CASE

    WHEN @AuditLevel = 0 THEN 'None'

    WHEN @AuditLevel = 1 THEN 'Successful logins only'

    WHEN @AuditLevel = 2 THEN 'Failed logins only'

    WHEN @AuditLevel = 3 THEN 'Both successful and failed logins'

    ELSE 'Unknown'

    END

    PRINT '* Detected - Audit Level --> ' + @AuditLvltxt

    IF (SELECT CONVERT(char(30), SERVERPROPERTY('ISSingleUser'))) = 1

    SET @ISSingleUser = 'Single User'

    ELSE

    SET @ISSingleUser = 'Multi User'

    PRINT '* Detected - User Mode --> '+@ISSingleUser

    SET @FileStreams = CONVERT(NVARCHAR(10), (SELECT VALUE FROM SYS.CONFIGURATIONS where Name like 'filestream access%'))

    IF (SELECT @FileStreams) = 1

    PRINT '* Detected - FileStreams --> Enabled'

    ELSE

    PRINT '* Detected - FileStreams --> Disabled'

    SET @BackUpCompression = CONVERT(NVARCHAR(10), (SELECT VALUE FROM SYS.CONFIGURATIONS where Name like 'backup compression%'))

    IF (SELECT @BackUpCompression) = 1

    PRINT '* Detected - Backup Compression --> Enabled'

    ELSE

    PRINT '* Detected - Backup Compression --> Disabled'

    SET @COLLATION = (SELECT CONVERT(char(30), SERVERPROPERTY('COLLATION')))

    PRINT '* Detected - Collation Type --> '+@COLLATION

    SET @ErrorLogLocation = (SELECT REPLACE(CAST(SERVERPROPERTY('ErrorLogFileName') AS VARCHAR(500)), 'ERRORLOG',''))

    PRINT '* Detected - SQL Server Errorlog Location --> ' +@ErrorLogLocation

    SET @DefaultTraceEnabled = CONVERT(NVARCHAR(1), (SELECT VALUE FROM SYS.CONFIGURATIONS where Name like 'default trace%'))

    IF (SELECT @DefaultTraceEnabled) = 1

    PRINT '* Detected - Default Trace File --> Enabled'

    ELSE

    PRINT '* Detected - Default Trace File --> Disabled'

    SET @TraceFileLocation = (SELECT REPLACE(CONVERT(VARCHAR(100),SERVERPROPERTY('ErrorLogFileName')), '\ERRORLOG','\log.trc'))

    PRINT '* Detected - SQL Server Default Trace Location --> ' +@TraceFileLocation

    CREATE TABLE #TraceStats

    (TraceFlag INT, [Status] INT, [Global] INT, [Session] INT);

    INSERT INTO #TraceStats

    EXEC ('DBCC TRACESTATUS WITH NO_INFOMSGS')

    IF (SELECT COUNT(*) FROM #TraceStats) = 0

    BEGIN

    PRINT '* Detected - Trace Flags Setting --> No Trace Flags settings detected'

    END

    ELSE

    BEGIN

    PRINT '* Detected - Trace Flags Setting --> Trace Flags Detected'

    DECLARE @TraceFlagValue NVARCHAR(10)

    ,@TraceFlagStatus NVARCHAR(10)

    ,@TraceFlagGlobal NVARCHAR(10)

    ,@TraceFlagSession NVARCHAR(10)

    DECLARE TraceFlagSet CURSOR LOCAL FAST_FORWARD FOR (SELECT TraceFlag, [Status], [Global], [Session] FROM #TraceStats)

    OPEN TraceFlagSet

    FETCH NEXT FROM TraceFlagSet INTO @TraceFlagValue, @TraceFlagStatus, @TraceFlagGlobal, @TraceFlagSession

    WHILE @@FETCH_STATUS = 0

    BEGIN

    IF @TraceFlagGlobal = 0

    BEGIN

    SET @TraceFlagGlobal = 'No'

    END

    ELSE

    SET @TraceFlagGlobal = 'Yes'

    IF @TraceFlagSession = 0

    BEGIN

    SET @TraceFlagSession = 'No'

    END

    ELSE

    SET @TraceFlagSession = 'Yes'

    PRINT' Using TraceFlag = ' +@TraceFlagValue

    PRINT' Status = ' +@TraceFlagStatus

    PRINT' Use Globally = '+@TraceFlagGlobal

    PRINT' Used in Session = '+ @TraceFlagSession

    FETCH NEXT FROM TraceFlagSet INTO @TraceFlagValue, @TraceFlagStatus, @TraceFlagGlobal, @TraceFlagSession

    END

    CLOSE TraceFlagSet

    DEALLOCATE TraceFlagSet

    END

    SET @LinkServers = (SELECT COUNT(*) FROM sys.servers WHERE is_linked ='1')

    PRINT '* Detected - Number of Link Servers --> ' +@LinkServers

    PRINT ''

    PRINT '* Detected - SysAdmin Members'

    PRINT ' ----------------------------'

    IF (SELECT COUNT(*) FROM sys.server_principals WHERE IS_SRVROLEMEMBER('sysadmin', name) = 1) = 0

    BEGIN

    PRINT ''

    PRINT '** No Sysadmin Accounts Detected ** '

    END

    ELSE

    BEGIN

    SELECT CONVERT (NVARCHAR(40), name) COLLATE DATABASE_DEFAULT as 'SysAdmin '

    into #SysAdminAccount FROM sys.server_principals WHERE IS_SRVROLEMEMBER('sysadmin', name) = 1

    DECLARE @AdminAccounts VARCHAR(50)

    DECLARE SysAccounts CURSOR LOCAL FAST_FORWARD FOR (SELECT SysAdmin from #SysAdminAccount)

    OPEN SysAccounts

    FETCH NEXT FROM SysAccounts INTO @AdminAccounts

    WHILE @@FETCH_STATUS = 0

    BEGIN

    PRINT'SysAdmin Account - ' +@AdminAccounts

    FETCH NEXT FROM SysAccounts INTO @AdminAccounts

    END

    CLOSE SysAccounts

    DEALLOCATE SysAccounts

    END

    PRINT ''

    PRINT '* Detected - ServerAdmin Members'

    PRINT ' ------------------------------'

    IF (SELECT COUNT(*) FROM sys.server_principals r

    JOIN sys.server_role_members m ON r.principal_id = m.role_principal_id

    JOIN sys.server_principals p ON p.principal_id = m.member_principal_id

    WHERE (r.type ='R')and(r.name='serveradmin')) < 1

    BEGIN

    PRINT '** No ServerAdmin Accounts Detected ** '

    END

    ELSE

    BEGIN

    SELECT CONVERT (NVARCHAR(50),p.name) as 'ServerAdmins' INTO #SrvAdmin

    FROM sys.server_principals r

    JOIN sys.server_role_members m ON r.principal_id = m.role_principal_id

    JOIN sys.server_principals p ON p.principal_id = m.member_principal_id

    WHERE (r.type ='R') AND (r.name='serveradmin')

    DECLARE @SrvAdmins VARCHAR(50)

    DECLARE SrvAccounts CURSOR LOCAL FAST_FORWARD FOR (SELECT ServerAdmins FROM #SrvAdmin)

    OPEN SrvAccounts

    FETCH NEXT FROM SrvAccounts INTO @SrvAdmins

    WHILE @@FETCH_STATUS = 0

    BEGIN

    PRINT'Server Admin Account - ' +@SrvAdmins

    FETCH NEXT FROM SrvAccounts INTO @SrvAdmins

    END

    CLOSE SrvAccounts

    DEALLOCATE SrvAccounts

    END

    PRINT ''

    /*-- The checks for the following setting are to ensure SQL Server is running without dangerous setting. These setting should only be done if recommend

    by Microsoft support or recommend to correct a performance issue

    affinity64 mask affinity I/O mask affinity64 I/O mask lightweight pooling priority boost max worker threads

    */

    SELECT [name], [description], [value_in_use] INTO #SQL_Server_Settings

    FROM master.sys.configurations

    where [name] = 'affinity64 mask' -- performance

    OR [name] = 'affinity I/O mask' -- performance

    OR [name] = 'affinity64 I/O mask' -- performance

    OR [name] = 'lightweight pooling' -- performance

    OR [name] = 'priority boost' -- performance

    OR [name] = 'max worker threads' -- performance

    OR [name] = 'ad hoc distributed queries'-- audit

    OR [name] = 'clr enabled' -- audit

    OR [name] = 'Cross db ownership chaining' -- audit

    OR [name] = 'Database Mail XPs' -- audit

    OR [name] = 'Ole Automation Procedures' -- audit

    OR [name] = 'Remote access' -- audit

    OR [name] = 'Scan for startup procs' -- audit

    OR [name] = 'xp_cmdshell' -- audit

    PRINT ''

    PRINT '-------------------------------------- Automated Checks/Tests Analysis --------------------------------------------------------- '

    PRINT ''

    CREATE TABLE #TrustedDB

    (name VARCHAR (50), DBTrusted VARCHAR(50));

    INSERT INTO #TrustedDB

    EXEC ('SELECT sys.server_principals.name as Owner, sys.databases.name FROM sys.databases

    LEFT OUTER JOIN sys.server_principals ON sys.databases.owner_sid = sys.server_principals.sid

    WHERE is_trustworthy_on = 1')

    IF (SELECT COUNT(*) FROM #TrustedDB) > 1

    BEGIN

    PRINT ' * Detected setting for TRUSTWORTHY databases --> Security Audit FAILED/WARNING --> Read Recommendations! ***'

    PRINT ''

    PRINT ''

    PRINT ' Reason: With a database set to TRUSTWORTHY, it will allow a user(s) to impersonate server level permissions. This setting can have harmful potential.'

    PRINT ' Because of the potential to use instance level security from inside of the database extreme care should be taken when granting access to one'

    PRINT ' of these databases. With the right access a user in a trusted database can take over the instance.'

    PRINT ''

    PRINT ' * If you restore or attach a database this setting is automatically turned off.'

    PRINT ' * MSDB is the only database with TRUSTWORTHY automatically set on and is required by the system. Altering this setting from its default value'

    PRINT ' can result in unexpected behavior by SQL Server components that use the MSDB database'

    PRINT ''

    PRINT ' Leave this setting set to OFF to mitigate certain threats that may be present when a database is attached to the server and the following'

    PRINT ' conditions are true: The database contains malicious assemblies that have an EXTERNAL_ACCESS or UNSAFE permission setting'

    PRINT ''

    PRINT ' For any TRUSTWORTHY database detected, carefully check the permissions of the database owner and those of the users of the database.'

    PRINT ' If the TRUSTWORTHY setting is set to ON, and if the owner of the database is a member of a group that has administrative credentials, such as'

    PRINT ' the sysadmin group, the database owner may be able to create and to run unsafe assemblies that can compromise the instance of SQL Server.'

    PRINT ''

    PRINT ' Best practices for database ownership and trust include the following:'

    PRINT ' *Have distinct owners for databases. Not all databases should be owned by the system administrator.'

    PRINT ' *Limit the number of owners for each database.'

    PRINT ' *Confer trust selectively.'

    PRINT ' *Leave the Cross-Database Ownership Chaining setting set to OFF unless multiple databases are deployed at a single unit.'

    PRINT ' *Migrate usage to selective trust instead of using the TRUSTWORTHY property.'

    PRINT ''

    PRINT ' References the following site: https://support.microsoft.com/en-us/kb/2183687 '

    PRINT ''

    INSERT INTO #SASATFailed SELECT 'TRUSTWORTHY Databases'

    SELECT ' ', DBTrusted AS 'TRUSTWORTHY Database Name', name as 'Database Owner' FROM #TrustedDB

    PRINT ''

    END

    ELSE

    IF (SELECT DBTrusted FROM #TrustedDB) = 'msdb'

    BEGIN

    SET @TestResultCounter = @TestResultCounter + 1

    PRINT ' * Detected setting for TRUSTWORTHY databases --> Possible Dangerous Setting - Only MSDB is set to TRUSTWORTHY - PASSED'

    PRINT ' NOTE: MSDB is the only database with TRUSTWORTHY automatically set on and is required by the system.'

    PRINT ''

    END

    DECLARE @Valuedescript NVARCHAR(100), @ValueName2 NVARCHAR (100), @ValueInUse NVARCHAR (100)

    DECLARE DangerousSettings

    CURSOR FOR SELECT [description] ,[name] ,CONVERT(NVARCHAR (100),[value_in_use]) FROM #SQL_Server_Settings

    OPEN DangerousSettings

    FETCH NEXT FROM DangerousSettings INTO @Valuedescript, @ValueName2,@ValueInUse

    WHILE @@FETCH_STATUS = 0

    BEGIN

    IF @ValueInUse = 0

    BEGIN

    PRINT ' * Detected setting for '''+ @ValueName2 +''' = '+ @ValueInUse + ' --> Possible Dangerous Setting - PASSED'

    SET @TestResultCounter = @TestResultCounter + 1

    END

    ELSE

    BEGIN

    PRINT ' * Detected setting for ''' + @Valuedescript +''' --> *** FAILED/WARNING. Possible Dangerous Setting --> Is set to '+@ValueInUse + ' ***'

    IF @ValueName2 = 'max worker threads'

    BEGIN

    PRINT ''

    PRINT ' Max Work Threads setting my cause blocking and thread pool issues/errors.'

    PRINT ''

    PRINT ' When all worker threads are active with long running queries, SQL Server may appear unresponsive until'

    PRINT ' a worker thread completes and becomes available. Though not a defect, this can sometimes be undesirable.'

    PRINT ' If a process appears to be unresponsive and no new queries can be processed, then connect to SQL Server'

    PRINT ' using the dedicated administrator connection (DAC), and kill the process.'

    PRINT ''

    PRINT ' ** Only use if requested by Microsoft Support ** The default value for this option in sp_configure is 0.'

    PRINT ''

    INSERT INTO #SASATFailed SELECT 'Max Work Threads'

    END

    IF @ValueName2 = 'priority boost'

    BEGIN

    PRINT ''

    PRINT '"Boost SQL Server priority" setting will drain OS and network functions and causes issues/errors.'

    PRINT ''

    PRINT ' Raising the priority too high may drain resources from essential operating system and network functions, '

    PRINT ' resulting in problems shutting down SQL Server or using other operating system tasks on the server. '

    PRINT ''

    PRINT ' ** Only use if requested by Microsoft Support ** The default value for this option in sp_configure is 0.'

    PRINT ''

    INSERT INTO #SASATFailed SELECT 'Priority Boost'

    END

    IF @ValueName2 = 'lightweight pooling'

    BEGIN

    PRINT ''

    PRINT '"Use Windows fibers (lightweight pooling)". By setting lightweight pooling to 1 causes SQL Server to switch to fiber mode scheduling. '

    PRINT ''

    PRINT ' Common language runtime (CLR) execution is not supported under lightweight pooling. Disable one of two options: "clr enabled" or "lightweight pooling". '

    PRINT ' Features that rely upon CLR and that do not work properly in fiber mode include the hierarchy data type, replication, and Policy-Based Management.'

    PRINT ' CLR, replication and extended stored procedures will fail and/or not work.'

    PRINT ''

    PRINT ' ** Only use if requested by Microsoft Support ** The default value for this option in sp_configure is 0.'

    PRINT ''

    INSERT INTO #SASATFailed SELECT 'Lightweight Pooling'

    END

    IF @ValueName2 like 'affinity%'

    BEGIN

    PRINT ''

    PRINT ' I/O and processor affinity changes will cause strange issues/errors and is not necessary on and 64 bit server.'

    PRINT ''

    PRINT ' Do not configure CPU affinity in the Windows operating system and also configure the affinity mask in SQL Server.'

    PRINT ' These settings are attempting to achieve the same result, and if the configurations are inconsistent, you may have'

    PRINT ' unpredictable results. SQL Server CPU affinity is best configured using the sp_configure option in SQL Server.'

    PRINT ' Using the GUI, under server properties select the "Automatically set processor affinity mask for all processors" and'

    PRINT ' select the "Automatically set I/O affinity mask for all processors". This will correct the issues.'

    PRINT ''

    PRINT ' ** Only use if requested by Microsoft Support **The default value for this option in sp_configure is 0.'

    PRINT ''

    INSERT INTO #SASATFailed SELECT 'I/O and Processor Affinity'

    END

    IF @ValueName2 = 'ad hoc distributed queries'

    BEGIN

    PRINT ''

    PRINT ' Reason: Enabling the use of ad hoc names means that any authenticated login to SQL Server can access the provider.'

    PRINT ' SQL Server administrators should enable this feature for providers that are safe to be accessed by any local login.'

    PRINT ' By default, SQL Server does not allow ad hoc distributed queries using OPENROWSET and OPENDATASOURCE'

    PRINT ''

    PRINT ' Recommended changes: '

    PRINT ''

    PRINT ' EXECUTE sp_configure ''show advanced options'', 1; '

    PRINT ' GO '

    PRINT ' RECONFIGURE; '

    PRINT ' GO '

    PRINT ' EXECUTE sp_configure ''Ad Hoc Distributed Queries'', 0; '

    PRINT ' GO '

    PRINT ' RECONFIGURE; '

    PRINT ' GO '

    PRINT ' EXECUTE sp_configure ''show advanced options'', 0; '

    PRINT ' RECONFIGURE;'

    PRINT ' GO '

    PRINT ''

    PRINT ' This change will take effect immediately.'

    PRINT ''

    PRINT' References: '

    PRINT ' 1. https://msdn.microsoft.com/en-us/library/ms187569(v=sql.120).aspx '

    PRINT ' 2. https://msdn.microsoft.com/en-us/library/ms187569(v=sql.110).aspx'

    PRINT ''

    INSERT INTO #SASATFailed SELECT 'Ad Hoc Distributed queries'

    END

    IF @ValueName2 = 'clr enabled'

    BEGIN

    PRINT ''

    PRINT ' Reason: Enabling the use of CLR assemblies can increase SQL Server attack surface and puts it at risk from malicious assemblies. '

    PRINT ' Use the clr enabled option to specify whether user assemblies can be run by SQL Server '

    PRINT ''

    PRINT ' Recommended changes: '

    PRINT ''

    PRINT ' EXECUTE sp_configure ''clr enabled'', 0; '

    PRINT ' GO '

    PRINT ' RECONFIGURE;'

    PRINT ' GO '

    PRINT ''

    PRINT ' Both value columns must show 0. Default Value: 0 (disabled). This change will take effect immediately. '

    PRINT''

    PRINT' References: '

    PRINT ' 1. https://msdn.microsoft.com/en-us/library/ms175193(v=sql.120).aspx'

    PRINT ' 2. https://msdn.microsoft.com/en-us/library/ms175193(v=sql.110).aspx'

    PRINT ''

    INSERT INTO #SASATFailed SELECT 'CLR Enabled'

    END

    IF @ValueName2 = 'Cross db ownership chaining'

    BEGIN

    PRINT ''

    PRINT ' This server option allows you to control cross-database ownership chaining at the database level or to allow '

    PRINT ' cross-database ownership chaining for all databases'

    PRINT ''

    PRINT ' Recommended changes: '

    PRINT ''

    PRINT ' EXECUTE sp_configure ''Cross db ownership chaining'', 0; ;'

    PRINT ' GO '

    PRINT ' RECONFIGURE; '

    PRINT ' GO '

    PRINT ''

    PRINT ' Both value columns must show 0. Default Value: 0 (disabled) '

    PRINT ''

    PRINT' References: '

    PRINT ' 1. https://msdn.microsoft.com/en-us/library/ms188694(v=sql.120).aspx'

    PRINT ' 2. https://msdn.microsoft.com/en-us/library/ms188694(v=sql.110).aspx'

    PRINT ''

    INSERT INTO #SASATFailed SELECT 'Cross DB Ownership Chaining'

    END

    IF @ValueName2 = 'Database Mail XPs'

    BEGIN

    PRINT ''

    PRINT ' Reason: Disabling Database Mail reduces the SQL Server surface, eliminates a DOS attack vector and helps prevent '

    PRINT ' data to be sent to non-trusted parties. Allows the creation and sending of email messages from SQL Server'

    PRINT ' to anyone and anywhere. '

    PRINT ''

    PRINT ' Recommended changes: '

    PRINT ''

    PRINT ' EXECUTE sp_configure ''show advanced options'', 1; '

    PRINT ' GO '

    PRINT ' RECONFIGURE; '

    PRINT ' GO '

    PRINT ' EXECUTE sp_configure ''Database Mail XPs'', 0; '

    PRINT ' GO '

    PRINT ' RECONFIGURE; '

    PRINT ' GO '

    PRINT ' EXECUTE sp_configure ''show advanced options'', 0;'

    PRINT ' GO '

    PRINT ' RECONFIGURE; '

    PRINT ' GO '

    PRINT ''

    PRINT ' Both value columns must show 0. Default Value: 0 (disabled). This change will take effect immediately. '

    PRINT ''

    PRINT ' References the following site: https://msdn.microsoft.com/en-us/library/ms189635(v=sql.120).aspx '

    PRINT ''

    INSERT INTO #SASATFailed SELECT 'Database Mail XPs'

    END

    IF @ValueName2 = 'Ole Automation Procedures'

    BEGIN

    PRINT ''

    PRINT ' Reason: Enabling this option increases the SQL Server attack surface and allows users to execute functions in the '

    PRINT ' security context. Use the Ole Automation Procedures option to specify whether OLE Automation objects can be '

    PRINT ' instantiated within Transact-SQL batches '

    PRINT ''

    PRINT ' Recommended changes: '

    PRINT ''

    PRINT ' EXECUTE sp_configure ''show advanced options'', 1; '

    PRINT ' GO '

    PRINT ' RECONFIGURE; '

    PRINT ' GO '

    PRINT ' EXECUTE sp_configure ''Ole Automation Procedures'', 0; '

    PRINT ' GO '

    PRINT ' RECONFIGURE; '

    PRINT ' GO '

    PRINT ' EXECUTE sp_configure ''show advanced options'', 0; '

    PRINT ' GO '

    PRINT ' RECONFIGURE; '

    PRINT ' GO '

    PRINT ''

    PRINT ' Both value columns must show 0. Default Value: 0 (disabled). This change will take effect immediately. '

    PRINT ''

    PRINT' References: '

    PRINT ' 1. https://msdn.microsoft.com/en-us/library/ms191188(v=sql.120).aspx'

    PRINT ' 2. https://msdn.microsoft.com/en-us/library/ms191188(v=sql.110).aspx'

    PRINT ''

    INSERT INTO #SASATFailed SELECT 'OLE Automation Procedures'

    END

    IF @ValueName2 = 'Remote access'

    BEGIN

    PRINT ''

    PRINT ' Reason: Could be used to launch a Denial-of-Service (DoS) attack on remote server(s). Controls the execution'

    PRINT ' of stored procedures from local and / or remote servers on which instances of SQL Server are running.'

    PRINT ''

    PRINT ' Recommended changes: '

    PRINT ''

    PRINT ' EXECUTE sp_configure ''show advanced options'', 1; '

    PRINT ' GO '

    PRINT ' RECONFIGURE; '

    PRINT ' GO '

    PRINT ' EXECUTE sp_configure ''Remote access'', 0; '

    PRINT ' GO '

    PRINT ' RECONFIGURE; '

    PRINT ' GO '

    PRINT ' EXECUTE sp_configure ''show advanced options'', 0; '

    PRINT ' GO '

    PRINT ' RECONFIGURE; '

    PRINT ' GO '

    PRINT ''

    PRINT ' Value must show 0. Default Value: 1 (enabled). This change will require a restart to take effect. '

    PRINT ''

    PRINT ' References the following site: http://msdn.microsoft.com/en-us/library/ms187660(v=sql.105).aspx '

    PRINT ''

    INSERT INTO #SASATFailed SELECT 'Remote Access'

    END

    IF @ValueName2 = 'Scan for startup procs'

    BEGIN

    PRINT ''

    PRINT ' Reason: Reduces the risk auto execution of malicious code. Option allows SQL Server to'

    PRINT ' automatically execute stored procedure(s) on SQL Server services startup / restart'

    PRINT ''

    PRINT ' Recommended changes: '

    PRINT ''

    PRINT ' EXECUTE sp_configure ''show advanced options'', 1; '

    PRINT ' GO '

    PRINT ' RECONFIGURE; '

    PRINT ' GO '

    PRINT ' EXECUTE sp_configure ''Scan for startup procs'', 0; '

    PRINT ' GO '

    PRINT ' RECONFIGURE; '

    PRINT ' GO '

    PRINT ' EXECUTE sp_configure ''show advanced options'', 0; '

    PRINT ' GO '

    PRINT ' RECONFIGURE; '

    PRINT ' GO '

    PRINT ''

    CREATE TABLE #AutoStart

    (AutoSPName NVARCHAR(50)

    ,AutoSchema NVARCHAR(2)

    ,AutoExecValue NVARCHAR(2));

    INSERT INTO #AutoStart (AutoSPName, AutoSchema, AutoExecValue)

    SELECT [name], [schema_id], [is_auto_executed] FROM master.sys.procedures

    WHERE is_auto_executed = 1

    ORDER BY modify_date DESC;

    DECLARE @AutoSPName NVARCHAR(50)

    ,@AutoSchema VARCHAR(2)

    ,@AutoExecValue VARCHAR(2);

    DECLARE AUTOSPCHECK CURSOR LOCAL FAST_FORWARD FOR

    (SELECT AutoSPName, AutoSchema, AutoExecValue FROM #AutoStart)

    OPEN AUTOSPCHECK

    FETCH NEXT FROM AUTOSPCHECK INTO @AutoSPName, @AutoSchema, @AutoExecValue

    WHILE @@FETCH_STATUS = 0

    BEGIN

    PRINT' ** Check the following - Auto Start Stored Procedures: '+@AutoSPName+@AutoSchema+@AutoExecValue

    FETCH NEXT FROM AUTOSPCHECK INTO @AutoSPName, @AutoSchema, @AutoExecValue

    END

    CLOSE AUTOSPCHECK;

    DEALLOCATE AUTOSPCHECK;

    PRINT ''

    PRINT ' Both value columns must show 0. Default Value: 0 (disabled). This change will require a restart to take effect. '

    PRINT ''

    PRINT ' References the following site: http://msdn.microsoft.com/en-us/library/ms179460(v=sql.105).aspx '

    PRINT ''

    INSERT INTO #SASATFailed SELECT 'Scan for startup stored procedures'

    END

    IF @ValueName2 = 'xp_cmdshell'

    BEGIN

    PRINT ''

    PRINT ' Reason: xp_cmdshell is commonly used by attackers to read or write data to/from the underlying Operating System of'

    PRINT ' a database server. This option allows SQL Server user to execute commands in the operating system and returns '

    PRINT ' with the SQL client'

    PRINT ''

    PRINT ' Recommended changes: '

    PRINT ''

    PRINT ' EXECUTE sp_configure ''show advanced options'', 1; '

    PRINT ' GO '

    PRINT ' RECONFIGURE; '

    PRINT ' GO '

    PRINT ' EXECUTE sp_configure ''xp_cmdshell'', 0; '

    PRINT ' GO '

    PRINT ' RECONFIGURE; '

    PRINT ' GO '

    PRINT ' EXECUTE sp_configure ''show advanced options'', 0; '

    PRINT ' GO '

    PRINT ' RECONFIGURE; '

    PRINT ' GO '

    PRINT ''

    PRINT ' Both value columns must show 0. Default Value: 0 (disabled). This change will take effect immediately. '

    PRINT ''

    PRINT ' References the following site: https://msdn.microsoft.com/en-us/library/ms175046(v=sql.110).aspx'

    PRINT ''

    INSERT INTO #SASATFailed SELECT 'XP_cmdshell Enabled'

    END

    END

    PRINT ' '

    FETCH NEXT FROM DangerousSettings INTO @Valuedescript, @ValueName2,@ValueInUse

    END

    CLOSE DangerousSettings

    DEALLOCATE DangerousSettings

    IF ((SELECT [is_disabled] FROM sys.server_principals WHERE sid = 0x01) = 0)

    BEGIN

    PRINT ' * Detected setting for ''sa'' account --> Security Audit FAILED. --> *** FAILED/WARNING. Possible Dangerous Setting --> Is set to ENABLE ***'

    PRINT ''

    PRINT ' Reason: Disabling this account reduces the risk of an attacker executing a brute\force attacks against SQL Server.'

    PRINT ' The sa account is generally known and has high permissions like sysadmin. It is bad security practice for'

    PRINT ' applications and/or scripts connect with the sa account. If this has been done, however, disabling the account '

    PRINT ' will prevent applications and/or scripts from functioning properly. In this case you must leave the account enable. '

    PRINT ' It is recommend that other audit tools should be used to trace the usage/use of the ''sa'' account'

    PRINT ''

    PRINT ' Recommend changes: Execute the following query to disable the ''sa'' account'

    PRINT ''

    PRINT ' ALTER LOGIN sa DISABLE; '

    PRINT ''

    PRINT ' By default the ''sa'' login account is enabled. '

    PRINT ''

    PRINT ' References the following site: https://msdn.microsoft.com/en-us/library/ms188786(v=sql.110).aspx'

    PRINT ''

    INSERT INTO #SASATFailed SELECT 'SA Account Enabled'

    END

    ELSE

    BEGIN

    PRINT ' * Detected setting for ''sa'' account is DISABLED --> Possible Dangerous Setting - PASSED'

    PRINT ''

    SET @TestResultCounter = @TestResultCounter + 1;

    END

    SET @RemoteAdminConnections = CONVERT(NVARCHAR(10), (SELECT VALUE FROM SYS.CONFIGURATIONS where Name like 'Remote admin connections%'))

    IF (SELECT @RemoteAdminConnections) = 1

    BEGIN

    PRINT ' * Detected setting for ''Remote Admin Connections'' = 0 --> Security Audit PASSED'

    SET @TestResultCounter = @TestResultCounter + 1

    PRINT ''

    END

    ELSE

    BEGIN

    PRINT ' * Detected setting for ''Remote Admin Connections'' = 0 --> Security Audit FAILED --> Change this setting back to default! ***'

    PRINT ''

    PRINT ' Reason: The Dedicated Admin Connection (DAC) is a feature that allows connections to have direct access to system '

    PRINT ' tables which could be used to conduct malicious activities. This feature must be restricted for only local '

    PRINT ' administrators to reduce the risk. '

    PRINT ''

    PRINT ' Recommended changes: '

    PRINT ''

    PRINT ' EXECUTE sp_configure ''show advanced options'', 1; '

    PRINT ' GO '

    PRINT ' RECONFIGURE; '

    PRINT ' GO '

    PRINT ' EXECUTE sp_configure ''Remote admin connections'', 1; '

    PRINT ' GO '

    PRINT ' RECONFIGURE; '

    PRINT ' GO '

    PRINT ' EXECUTE sp_configure ''show advanced options'', 0; '

    PRINT ' GO '

    PRINT ' RECONFIGURE; '

    PRINT ' GO '

    PRINT ''

    PRINT ' Both value columns must show 1 on clustered installations. Default Value: 0 (disabled). This change will take effect immediately. '

    PRINT ''

    PRINT' References: '

    PRINT ' 1. https://msdn.microsoft.com/en-us/library/ms190468(v=sql.120).aspx'

    PRINT ' 2. https://msdn.microsoft.com/en-us/library/ms190468(v=sql.110).aspx'

    PRINT ''

    INSERT INTO #SASATFailed SELECT 'Remote Admin Connections'

    END

    SET @DefaultTraceEnabled = CONVERT(NVARCHAR(1), (SELECT VALUE FROM SYS.CONFIGURATIONS where Name like 'default trace%'))

    IF (SELECT @DefaultTraceEnabled) = 1

    BEGIN

    PRINT ' * Detected setting for ''Default Trace File Enabled'' = 1 --> Security Audit PASSED'

    SET @TestResultCounter = @TestResultCounter + 1

    PRINT ''

    END

    ELSE

    BEGIN

    PRINT ' * Detected setting for ''Default Trace File Enabled'' = 0 --> Security Audit FAILED Default Trace is set to 1 --> Change this setting back to default! ***'

    PRINT''

    PRINT' Reason: Default trace allows for the collection of valuable audit information and security-related activities on the server.'

    PRINT' Default trace files provide audit logging of database activity including account activities, login privilege '

    PRINT' elevation and execution of DBCC commands. '

    PRINT ''

    PRINT ' Recommended changes: '

    PRINT ''

    PRINT ' EXECUTE sp_configure ''show advanced options'', 1; '

    PRINT ' GO '

    PRINT ' RECONFIGURE; '

    PRINT ' GO '

    PRINT ' EXECUTE sp_configure ''Default trace enabled'', 1; '

    PRINT ' GO '

    PRINT ' RECONFIGURE; '

    PRINT ' GO '

    PRINT ' EXECUTE sp_configure ''show advanced options'', 0; '

    PRINT ' GO '

    PRINT ' RECONFIGURE; '

    PRINT ' GO '

    PRINT''

    PRINT' Both value columns must show 1.Default Value: 1 (on). This change will take effect immediately. '

    PRINT ''

    PRINT' References: '

    PRINT ' 1. https://msdn.microsoft.com/en-us/library/ms175513(v=sql.120).aspx'

    PRINT ' 2. https://msdn.microsoft.com/en-us/library/ms175513(v=sql.110).aspx'

    PRINT ''

    INSERT INTO #SASATFailed SELECT 'Default Trace File Enabled'

    END

    SET @TRANSPORT = (SELECT net_transport FROM sys.dm_exec_connections WHERE session_id = @@SPID)

    IF (@TRANSPORT = 'Named pipe')

    BEGIN

    PRINT ' * Detected setting for ''Default SQL Port Number'' --> Connection made with '+@TRANSPORT+'. Manually check default port number if TCP/IP is enable'

    PRINT''

    END

    ELSE

    BEGIN

    IF (@StaticPortNumber = '1433')

    BEGIN

    PRINT ' * Detected setting for ''Default SQL Port Number'' --> Security Audit FAILED. Default SQL Port Number is set to '+@StaticPortNumber + ' --> Change setting! ***'

    PRINT ''

    PRINT ' Reason: Using a non-default port helps protect SQL Server from attacks directed to the default port. '

    PRINT ' Changing the default port will force DAC (Default Administrator Connection) to listen on a random port. Also, firewall'

    PRINT ' will require configuration changes. Default SQL Server instance are assigned port of TCP: 1433 for TCP/IP communication.'

    PRINT ' Since TCP: 1433 is a widely known for SQL Server port, the port number should be changed. '

    PRINT ' By default, SQL Server instances listen on TCP port 1433 and named instances uses dynamic ports.'

    PRINT ''

    PRINT' References: '

    PRINT ' 1. https://msdn.microsoft.com/en-us/library/ms177440(v=sql.120).aspx'

    PRINT ' 2. https://msdn.microsoft.com/en-us/library/ms177440(v=sql.110).aspx'

    PRINT ''

    INSERT INTO #SASATFailed SELECT 'Default Port Number'

    END

    ELSE

    BEGIN

    PRINT ' * Detected setting for ''Default SQL Port Number'' --> '+@StaticPortNumber +' --> Security Audit PASSED'

    PRINT ''

    SET @TestResultCounter = @TestResultCounter + 1

    END

    END

    SET @xp_dirtreeEnabled = (select 'PUBLIC' from sys.database_permissions

    where major_id = OBJECT_ID('xp_dirtree') AND [type] = 'EX' AND grantee_principal_id = 0 );

    IF @xp_dirtreeEnabled = 'PUBLIC'

    BEGIN

    PRINT ' * Detected setting for ''xp_dirtree'' --> Security Audit FAILED --> Apply Recommended changes! ***'

    PRINT ''

    PRINT ' Reason: Ensuring this procedure is disabled will prevent an attacker from performing directory enumeration and'

    PRINT ' listing files and folders to read or write data to / from. This procedure is currently leveraged by '

    PRINT ' several automated SQL Injection tools. Any record returned is an indicator that the public role '

    PRINT ' maintains execute permission on the procedure. Results returns a set of the directory tree for '

    PRINT ' a given directory path. '

    PRINT ''

    PRINT ' Recommended changes: '

    PRINT ''

    PRINT ' The following steps can be performed using SQL Server Management Studio: '

    PRINT ''

    PRINT ' 1. In Object Explorer, navigate to the SQL Server instance and expand the path: '

    PRINT ' Databases\System Databases\master\Programmability\Extended Stored '

    PRINT ' Procedures\System Extended Stored Procedures '

    PRINT ''

    PRINT ' 2. Locate xp_dirtree, right click and select Properties '

    PRINT ''

    PRINT ' 3. Select the Permissions tab '

    PRINT ''

    PRINT ' 4. If the ''public'' entry does not exist within the Users or Roles listing the server is in compliance with the '

    PRINT ' recommendation and you can halt further steps. If the ''public'' entry does exist proceed to step 5 '

    PRINT ''

    PRINT ' 5. Select the ''public'' entry within the Users or Roles listing '

    PRINT ''

    PRINT ' 6. If the Grant check box for the Execute permission is checked the Public role maintains Execute permission'

    PRINT ' on the procedure and the listed remediation procedure should be followed. '

    PRINT ''

    PRINT' Or you can execute the following to revoke use by all general users on the SQL Server machine: '

    PRINT ''

    PRINT ' REVOKE EXECUTE ON xp_dirtree TO PUBLIC;'

    PRINT ''

    PRINT ' Note: Server logins within the sysadmin role will retain use of this procedure. '

    PRINT ''

    INSERT INTO #SASATFailed SELECT 'XP_dirtree Enabled for PUBLIC'

    END

    ELSE

    BEGIN

    PRINT ' * Detected setting for ''xp_dirtree'' --> Security Audit PASSED'

    SET @TestResultCounter = @TestResultCounter + 1

    PRINT ''

    END

    SET @xp_fixeddrivesEnabled =(select 'PUBLIC' from sys.database_permissions where major_id =

    OBJECT_ID('xp_fixeddrives') AND [type] = 'EX' AND grantee_principal_id = 0);

    IF @xp_fixeddrivesEnabled = 'PUBLIC'

    BEGIN

    PRINT ' * Detected setting for ''xp_fixeddrives'' --> Security Audit FAILED --> Apply Recommended changes! ***'

    PRINT ''

    PRINT ' Reason: When disabled, will prevent an attacker from viewing local available drives for '

    PRINT ' directory and / or file enumeration. Any record returned indicates the public role maintains '

    PRINT ' execute permission on the procedure. A list of all hard drives on the machine and the space'

    PRINT ' free in megabytes for each drive are shown. '

    PRINT ''

    PRINT' Recommended changes: Revoke use by all general users on the SQL Server machine:'

    PRINT ''

    PRINT ' REVOKE EXECUTE ON xp_fixeddrives TO PUBLIC; '

    PRINT ''

    PRINT ' Note: Server logins within the sysadmin role will retain use of this procedure. '

    PRINT ''

    INSERT INTO #SASATFailed SELECT 'XP_fixdrdrives Enabled for PUBLIC'

    END

    ELSE

    BEGIN

    PRINT ' * Detected setting for ''xp_fixeddrives'' --> Security Audit PASSED'

    SET @TestResultCounter = @TestResultCounter + 1

    PRINT ''

    END

    SET @xp_enumgroupsEnabled = (select 'PUBLIC' from sys.database_permissions

    where major_id = OBJECT_ID('xp_enumgroups') AND [type] = 'EX' AND grantee_principal_id = 0 );

    IF @xp_enumgroupsEnabled = 'PUBLIC'

    BEGIN

    PRINT ' * Detected setting for ''xp_enumgroups'' --> Security Audit FAILED --> Apply Recommended changes! ***'

    PRINT ''

    PRINT ' Reason: Disabling this procedure will limit the ability to view Windows groups present on the'

    PRINT ' SQL Server machine. Currently being used by automated SQL Injection tools. '

    PRINT ' Any record returned indicates the public role maintains execute permission on the procedure.'

    PRINT ' This procedure can provide a list of local Microsoft Windows groups and / or a list of global groups'

    PRINT ' that are defined in a specified Windows machine. '

    PRINT ''

    PRINT ' The following steps can be performed by using SQL Server Management Studio: '

    PRINT ''

    PRINT ' 1. In Object Explorer, navigate to the SQL Server instance and expand the path: '

    PRINT ' Databases\System Databases\master\Programmability\Extended Stored '

    PRINT ' Procedures\System Extended Stored Procedures '

    PRINT ''

    PRINT ' 2. Locate xp_enumgroups, right click and select Properties '

    PRINT ''

    PRINT ' 3. Select the Permissions tab '

    PRINT ''

    PRINT ' 4. If the ''public'' entry does not exist within the Users or Roles listing the server is in compliance with the '

    PRINT ' recommendation and you can halt further steps. If the ''public'' entry does exist proceed to step 5 '

    PRINT ''

    PRINT ' 5. Select the ''public'' entry within the Users or Roles listing '

    PRINT ''

    PRINT ' 6. If the Grant check box for the Execute permission is checked the Public role maintains Execute permission on the '

    PRINT ' procedure and the listed remediation procedure should be followed. '

    PRINT ''

    PRINT ' Recommended changes: Revoke use by all general users on the SQL Server machine: '

    PRINT ''

    INSERT INTO #SASATFailed SELECT 'XP_enumgroups Enabled for PUBLIC'

    END

    ELSE

    BEGIN

    PRINT ' * Detected setting for ''xp_enumgroups'' --> Security Audit PASSED'

    SET @TestResultCounter = @TestResultCounter + 1

    PRINT ''

    END

    SET @xp_servicecontrolEnabled = (select 'PUBLIC' from sys.database_permissions

    where major_id = OBJECT_ID('xp_servicecontrol') AND [type] = 'EX' AND grantee_principal_id = 0 );

    IF @xp_servicecontrolEnabled = 'PUBLIC'

    BEGIN

    PRINT ' * Detected setting for ''xp_servicecontrol'' --> Security Audit FAILED --> Apply Recommended changes! ***'

    PRINT ''

    PRINT' Reason: Can be used remotely by an attacker to shutdown Windows services used by Antivirus products and / or firewalls'

    PRINT' Any record returned indicates the public role maintains execute permission on the procedure.'

    PRINT' Can be used to start and / or stop windows services and SQL related services running on the SQL Server machine. '

    PRINT ''

    PRINT ' The following steps can be used by using SQL Server Management Studio: '

    PRINT ''

    PRINT ' 1. In Object Explorer, navigate to the SQL Server instance and expand the path: '

    PRINT ' Databases\System Databases\master\Programmability\Extended Stored '

    PRINT ' Procedures\System Extended Stored Procedures '

    PRINT ''

    PRINT ' 2. Locate xp_servicecontrol, right click and select Properties '

    PRINT ''

    PRINT ' 3. Select the Permissions tab '

    PRINT ''

    PRINT ' 4. If the ''public'' entry does not exist within the Users or Roles listing the server is in compliance with the recommendation and '

    PRINT ' you can halt further steps. If the ''public'' entry does exist proceed to step 5 '

    PRINT ''

    PRINT ' 5. Select the ''public'' entry within the Users or Roles listing '

    PRINT ''

    PRINT ' 6. If the Grant check box for the Execute permission is checked the Public role maintains Execute permission on the procedure'

    PRINT ' and the listed remediation procedure should be followed.'

    PRINT ''

    PRINT' Recommended changes: '

    PRINT ''

    PRINT ' REVOKE EXECUTE ON xp_servicecontrol TO PUBLIC; '

    PRINT ''

    PRINT ' Note: Server logins within the sysadmin role will retain use of this procedure '

    PRINT' By default, the public role is given execute permissions to this procedure. '

    PRINT ''

    INSERT INTO #SASATFailed SELECT 'XP_servicecontrol Enabled for PUBLIC'

    END

    ELSE

    BEGIN

    PRINT ' * Detected setting for ''xp_servicecontrol'' --> Security Audit PASSED'

    SET @TestResultCounter = @TestResultCounter + 1

    PRINT ''

    END

    SET @xp_subdirsEnabled = (select 'PUBLIC' from sys.database_permissions

    where major_id = OBJECT_ID('xp_subdirs') AND [type] = 'EX' AND grantee_principal_id = 0 );

    IF @xp_subdirsEnabled = 'PUBLIC'

    BEGIN

    PRINT ' * Detected setting for ''xp_subdirs'' --> Security Audit FAILED --> Apply Recommended changes! ***'

    PRINT ''

    PRINT' Reason: Disable to prevent an attacker from performing directory enumeration, '

    PRINT' listing all subdirectories on the file system. The attacker could use this information to '

    PRINT' determine where key OS and SQL Server files are located. Shows all subdirectories'

    PRINT' with in a given folder or path. '

    PRINT' Any record returned indicates the public role maintains execute permission on the procedure.'

    PRINT''

    PRINT' The following steps can be used by using SQL Server Management Studio: '

    PRINT''

    PRINT' 1. In Object Explorer, navigate to the SQL Server instance and expand the path: '

    PRINT' Databases\System Databases\master\Programmability\Extended Stored '

    PRINT' Procedures\System Extended Stored Procedures '

    PRINT''

    PRINT' 2. Locate xp_subdirs, right click and select Properties '

    PRINT''

    PRINT' 3. Select the Permissions tab '

    PRINT''

    PRINT' 4. If the ''public'' entry does not exist within the Users or Roles listing the server is in compliance with the'

    PRINT' recommendation and you can halt further steps. If the ''public'' entry does exist proceed to step 5 '

    PRINT''

    PRINT' 5. Select the ''public'' entry within the Users or Roles listing '

    PRINT''

    PRINT' 6. If the Grant check box for the Execute permission is checked the Public role maintains Execute permission on the '

    PRINT' procedure and the listed remediation procedure should be followed. '

    PRINT ''

    PRINT' Recommended changes: Revoke use by all general users on the SQL Server machine: '

    PRINT ''

    PRINT ' REVOKE EXECUTE ON xp_subdirs TO PUBLIC; '

    PRINT ''

    PRINT ' Note: Server logins within the sysadmin role will retain use of this procedure. '

    PRINT' By default, the public role is not given execute permissions to this procedure. '

    PRINT ''

    INSERT INTO #SASATFailed SELECT 'XP_subdirs Enabled for PUBLIC'

    END

    ELSE

    BEGIN

    PRINT ' * Detected setting for ''xp_subdirs'' --> Security Audit PASSED'

    SET @TestResultCounter = @TestResultCounter + 1

    PRINT ''

    END

    SET @xp_regaddmultistringEnabled = (select 'PUBLIC' from sys.database_permissions

    where major_id = OBJECT_ID('xp_regaddmultistring') AND [type] = 'EX' AND grantee_principal_id = 0 );

    IF @xp_regaddmultistringEnabled = 'PUBLIC'

    BEGIN

    PRINT ' * Detected setting for ''xp_regaddmultistring'' --> Security Audit FAILED --> Apply Recommended changes! ***'

    PRINT ''

    PRINT ''

    PRINT' Reason: Disabling this feature will prevent a SQL Server users from writing to the Windows registry through SQL Server. '

    PRINT' Any record returned indicates the public role maintains execute permission on the procedure. '

    PRINT' Adds multiple strings to the server''s registry. '

    PRINT ''

    PRINT' The following steps can be used with SQL Server Management Studio: '

    PRINT ''

    PRINT' 1. In Object Explorer, navigate to the SQL Server instance and expand the path: '

    PRINT' Databases\System Databases\master\Programmability\Extended Stored '

    PRINT' Procedures\System Extended Stored Procedures '

    PRINT ''

    PRINT' 2. Locate xp_regaddmdmultistring, right click and select Properties '

    PRINT ''

    PRINT' 3. Select the Permissions tab '

    PRINT ''

    PRINT' 4. If the ''public'' entry does not exist within the Users or Roles listing the server is in compliance with the '

    PRINT' recommendation and you can halt further steps. If the ''public'' entry does exist proceed to step 5 '

    PRINT ''

    PRINT' 5. Select the ''public'' entry within the Users or Roles listing '

    PRINT ''

    PRINT' 6. If the Grant check box for the Execute permission is checked the Public role maintains Execute permission on the '

    PRINT' procedure and the listed remediation procedure should be followed. '

    PRINT ''

    PRINT' Recommended changes: Revoke the use by all general users on the SQL Server:'

    PRINT''

    PRINT ' REVOKE EXECUTE ON xp_regaddmultistring TO PUBLIC; '

    PRINT''

    PRINT ' Note: Logins within the sysadmin role will retain use of this procedure. '

    PRINT ''

    INSERT INTO #SASATFailed SELECT 'XP_regaddmultistring Enabled for PUBLIC'

    END

    ELSE

    BEGIN

    PRINT ' * Detected setting for ''xp_regaddmultistring'' --> Security Audit PASSED'

    SET @TestResultCounter = @TestResultCounter + 1

    PRINT ''

    END

    SET @xp_regdeletekeyEnabled = (select 'PUBLIC' from sys.database_permissions

    where major_id = OBJECT_ID('xp_regdeletekey') AND [type] = 'EX' AND grantee_principal_id = 0 );

    IF @xp_regdeletekeyEnabled = 'PUBLIC'

    BEGIN

    PRINT ' * Detected setting for ''xp_regdeletekey'' --> Security Audit FAILED --> Apply Recommended changes! ***'

    PRINT ''

    PRINT' Reason: Disabling this feature will prevent a SQL Server users from deleting values from the Windows registry through SQL Server. '

    PRINT' Any record returned indicates the public role maintains execute permission on the procedure. '

    PRINT' Ability to delete registry keys from the server''s registry.'

    PRINT ''

    PRINT' The following steps can be used with SQL Server Management Studio: '

    PRINT ''

    PRINT' 1. In Object Explorer, navigate to the SQL Server instance and expand the path: '

    PRINT' Databases\System Databases\master\Programmability\Extended Stored '

    PRINT' Procedures\System Extended Stored Procedures '

    PRINT ''

    PRINT' 2. Locate xp_regdeletekey, right click and select Properties '

    PRINT ''

    PRINT' 3. Select the Permissions tab '

    PRINT ''

    PRINT' 4. If the ''public'' entry does not exist within the Users or Roles listing the server is in compliance with the '

    PRINT' recommendation and you can halt further steps. If the ''public'' entry does exist proceed to step 5 '

    PRINT ''

    PRINT' 5. Select the ''public'' entry within the Users or Roles listing '

    PRINT ''

    PRINT' 6. If the Grant check box for the Execute permission is checked the Public role maintains Execute permission'

    PRINT' on the procedure and the listed remediation procedure should be followed. '

    PRINT ''

    PRINT' Recommended changes: Revoke use by all general users on the SQL Server machine: '

    PRINT ''

    PRINT ' REVOKE EXECUTE ON xp_regdeletekey TO PUBLIC; '

    PRINT ''

    PRINT ' Note: Logins within the sysadmin role will retain use of this procedure. '

    PRINT ''

    INSERT INTO #SASATFailed SELECT 'XP_regdeletekey Enabled for PUBLIC'

    END

    ELSE

    BEGIN

    PRINT ' * Detected setting for ''xp_regdeletekey'' --> Security Audit PASSED'

    SET @TestResultCounter = @TestResultCounter + 1

    PRINT ''

    END

    SET @xp_regdeletevalueEnabled = (select 'PUBLIC' from sys.database_permissions

    where major_id = OBJECT_ID('xp_regdeletevalue') AND [type] = 'EX' AND grantee_principal_id = 0 );

    IF @xp_regdeletevalueEnabled = 'PUBLIC'

    BEGIN

    PRINT ' * Detected setting for ''xp_regdeletevalue'' --> Security Audit FAILED --> Apply Recommended changes! ***'

    PRINT ''

    PRINT' Reason: Disabling this feature will prevent a SQL Server users from deleting values from the Windows registry through SQL Server. '

    PRINT' Any record returned indicates the public role maintains execute permission on the procedure '

    PRINT' Deletes values from the server''s registry.'

    PRINT''

    PRINT' The following steps can be used with SQL Server Management Studio: '

    PRINT ''

    PRINT' 1. In Object Explorer, navigate to the SQL Server instance and expand the path: '

    PRINT' Databases\System Databases\master\Programmability\Extended Stored '

    PRINT' Procedures\System Extended Stored Procedures '

    PRINT ''

    PRINT' 2. Locate xp_regdeletevalue, right click and select Properties '

    PRINT ''

    PRINT' 3. Select the Permissions tab '

    PRINT ''

    PRINT' 4. If the ''public'' entry does not exist within the Users or Roles listing the server is in compliance with the'

    PRINT' recommendation and you can halt further steps. If the ''public'' entry does exist proceed to step 5'

    PRINT ''

    PRINT' 5. Select the ''public'' entry within the Users or Roles listing '

    PRINT ''

    PRINT' 6. If the Grant check box for the Execute permission is checked the Public role maintains Execute permission '

    PRINT' on the procedure and the listed remediation procedure should be followed. '

    PRINT ''

    PRINT' Recommended changes: Revoke the use by all general users on the SQL Server machine: '

    PRINT ''

    PRINT ' REVOKE EXECUTE ON xp_regdeletevalue TO PUBLIC; '

    PRINT ''

    PRINT ' Note: Logins within the sysadmin role will retain use of this procedure. '

    PRINT ''

    INSERT INTO #SASATFailed SELECT 'XP_regdeletevalue Enabled for PUBLIC'

    END

    ELSE

    BEGIN

    PRINT ' * Detected setting for ''xp_regdeletevalue'' --> Security Audit PASSED'

    SET @TestResultCounter = @TestResultCounter + 1

    PRINT ''

    END

    SET @xp_regenumvaluesEnabled = (select 'PUBLIC' from sys.database_permissions

    where major_id = OBJECT_ID('xp_regenumvalues') AND [type] = 'EX' AND grantee_principal_id = 0 );

    IF @xp_regenumvaluesEnabled = 'PUBLIC'

    BEGIN

    PRINT ' * Detected setting for ''xp_regenumvalues'' --> Security Audit FAILED --> Apply Recommended changes! ***'

    PRINT ''

    PRINT' Reason: Disabling this feature will prevent a SQL Server user from enumerating and reading registry values. '

    PRINT' Any record returned indicates the public role maintains execute permission on the procedure. '

    PRINT' Enumerates and reads registry values from a provided registry path. '

    PRINT''

    PRINT' The following steps can be used with SQL Server Management Studio: '

    PRINT ''

    PRINT' 1. In Object Explorer, navigate to the SQL Server instance and expand the path: '

    PRINT' Databases\System Databases\master\Programmability\Extended Stored '

    PRINT' Procedures\System Extended Stored Procedures '

    PRINT ''

    PRINT' 2. Locate xp_regenumvalues, right click and select Properties '

    PRINT ''

    PRINT' 3. Select the Permissions tab '

    PRINT ''

    PRINT' 4. If the ''public'' entry does not exist within the Users or Roles listing the server is in compliance with the '

    PRINT' recommendation and you can halt further steps. If the ''public'' entry does exist proceed to step 5 '

    PRINT ''

    PRINT' 5. Select the ''public'' entry within the Users or Roles listing '

    PRINT ''

    PRINT' 6. If the Grant check box for the Execute permission is checked the Public role maintains Execute permission'

    PRINT' on the procedure and the listed remediation procedure should be followed. '

    PRINT ''

    PRINT' Recommended changes: Revoke use by all general users on the SQL Server machine: '

    PRINT''

    PRINT' REVOKE EXECUTE ON xp_regenumvalues TO PUBLIC; '

    PRINT''

    PRINT' Note: Logins within the sysadmin role will retain use of this procedure. '

    PRINT ''

    INSERT INTO #SASATFailed SELECT 'XP_regenumvalues Enabled for PUBLIC'

    END

    ELSE

    BEGIN

    PRINT ' * Detected setting for ''xp_regenumvalues'' --> Security Audit PASSED'

    SET @TestResultCounter = @TestResultCounter + 1

    PRINT ''

    END

    SET @xp_regremovemultistringEnabled = (select 'PUBLIC' from sys.database_permissions

    where major_id = OBJECT_ID('xp_regremovemultistring') AND [type] = 'EX' AND grantee_principal_id = 0 );

    IF @xp_regremovemultistringEnabled = 'PUBLIC'

    BEGIN

    PRINT ' * Detected setting for ''xp_regremovemultistring'' --> Security Audit FAILED --> Apply Recommended changes! ***'

    PRINT''

    PRINT' Reason: Disabling will prevent a SQL Server users from deleting batch values from the Windows registry via SQL Server. '

    PRINT' Any record returned indicates the public role maintains execute permission on the procedure. '

    PRINT' Removes multiple strings from the server''s registry. '

    PRINT ''

    PRINT' The following steps can be used with SQL Server Management Studio: '

    PRINT ''

    PRINT ' 1. In Object Explorer, navigate to the SQL Server instance and expand the path: '

    PRINT ' Databases\System Databases\master\Programmability\Extended Stored Procedures'

    PRINT ' \System Extended Stored Procedures '

    PRINT ''

    PRINT ' 2. Locate xp_regremovemultistring, right click and select Properties '

    PRINT ''

    PRINT ' 3. Select the Permissions tab '

    PRINT ''

    PRINT ' 4. If the ''public'' entry does not exist within the Users or Roles listing the server is in compliance with the '

    PRINT ' recommendation and you can halt further steps. If the ''public'' entry does exist proceed to step 5 '

    PRINT ''

    PRINT ' 5. Select the ''public'' entry within the Users or Roles listing '

    PRINT ''

    PRINT ' 6. If the Grant check box for the Execute permission is checked the Public role maintains Execute permission '

    PRINT ' on the procedure and the listed remediation procedure should be followed. '

    PRINT''

    PRINT' Recommended changes: Revoke the use by all general users on the SQL Server '

    PRINT''

    PRINT' REVOKE EXECUTE ON xp_regremovemultistring TO PUBLIC; '

    PRINT''

    PRINT' Note: Server logins within the sysadmin role will retain use of this procedure. '

    PRINT ''

    INSERT INTO #SASATFailed SELECT 'XP_regremovemultistring Enabled for PUBLIC'

    END

    ELSE

    BEGIN

    PRINT ' * Detected setting for ''xp_regremovemultistring'' --> Security Audit PASSED'

    SET @TestResultCounter = @TestResultCounter + 1

    PRINT ''

    END

    SET @xp_regwriteEnabled = (select 'PUBLIC' from sys.database_permissions

    where major_id = OBJECT_ID('xp_regwrite') AND [type] = 'EX' AND grantee_principal_id = 0 );

    IF @xp_regwriteEnabled = 'PUBLIC'

    BEGIN

    PRINT ' * Detected setting for ''xp_regwrite'' --> Security Audit FAILED --> Apply Recommended changes! ***'

    PRINT ''

    PRINT' Reason: Disabling will prevent a SQL Server users from writing to the Windows registry via SQL Server. '

    PRINT' Any record returned indicates the public role maintains execute permission on the procedure. '

    PRINT' Description: Writes key values to the server''s registry. '

    PRINT ''

    PRINT' The following steps can be used with SQL Server Management Studio: '

    PRINT ''

    PRINT ' 1. In Object Explorer, navigate to the SQL Server instance and expand the path: '

    PRINT ' Databases\System Databases\master\Programmability\Extended Stored '

    PRINT ' Procedures\System Extended Stored Procedures '

    PRINT ''

    PRINT ' 2. Locate xp_regwrite, right click and select Properties '

    PRINT ''

    PRINT ' 3. Select the Permissions tab '

    PRINT ''

    PRINT ' 4. If the ''public'' entry does not exist within the Users or Roles listing the server is in compliance with '

    PRINT ' the recommendation and you can halt further steps. If the ''public'' entry does exist proceed to step 5 '

    PRINT ''

    PRINT ' 5. Select the ''public'' entry within the Users or Roles listing '

    PRINT ''

    PRINT ' 6. If the Grant check box for the Execute permission is checked the Public role maintains Execute '

    PRINT ' permission on the procedure and the listed remediation procedure should be followed. '

    PRINT''

    PRINT' Recommended changes: Revoke use by all general users on the SQL Server machine: '

    PRINT''

    PRINT' REVOKE EXECUTE ON xp_regwrite TO PUBLIC; '

    PRINT''

    PRINT' Note: Server logins within the sysadmin role will retain use of this procedure. '

    PRINT ''

    INSERT INTO #SASATFailed SELECT 'XP_regwrite Enabled for PUBLIC'

    END

    ELSE

    BEGIN

    PRINT ' * Detected setting for ''xp_regwrite'' --> Security Audit PASSED'

    SET @TestResultCounter = @TestResultCounter + 1

    PRINT ''

    END

    SET @xp_regreadEnabled = (select 'PUBLIC' from sys.database_permissions

    where major_id = OBJECT_ID('xp_regread') AND [type] = 'EX' AND grantee_principal_id = 0 );

    IF @xp_regreadEnabled = 'PUBLIC'

    BEGIN

    PRINT ' * Detected setting for ''xp_regread'' --> Security Audit FAILED --> Apply Recommended changes! ***'

    PRINT ''

    PRINT' Reason: Disabling this feature will prevent a SQL Server users from enumerating'

    PRINT' and reading registry values. This procedure is used by several automated SQL injection tools. '

    PRINT' Any record returned indicates the public role maintains execute permission on the procedure. '

    PRINT' Description: Reads key values from the server''s registry. '

    PRINT ''

    PRINT' The following steps can be used with SQL Server Management Studio: '

    PRINT ''

    PRINT ' 1. In Object Explorer, navigate to the SQL Server instance and expand the path: '

    PRINT ''

    PRINT ' Databases\System Databases\master\Programmability\Extended Stored '

    PRINT ' Procedures\System Extended Stored Procedures '

    PRINT ''

    PRINT ' 2. Locate xp_regread, right click and select Properties '

    PRINT ''

    PRINT ' 3. Select the Permissions tab '

    PRINT ''

    PRINT ' 4. If the ''public'' entry does not exist within the Users or Roles listing the server is in compliance with the '

    PRINT ' recommendation and you can halt further steps. If the ''public'' entry does exist proceed to step 5 '

    PRINT ''

    PRINT ' 5. Select the ''public'' entry within the Users or Roles listing '

    PRINT ''

    PRINT ' 6. If the Grant check box for the Execute permission is checked the Public role maintains Execute'

    PRINT ' permission on the procedure and the listed remediation procedure should be followed. '

    PRINT''

    PRINT' Recommended changes: Revoke the use by all general users on the SQL Server: '

    PRINT''

    PRINT' REVOKE EXECUTE ON xp_regread TO PUBLIC; '

    PRINT''

    PRINT' Note: Logins within the sysadmin role will retain use of this procedure. '

    PRINT ''

    INSERT INTO #SASATFailed SELECT 'XP_regread Enabled for PUBLIC'

    END

    ELSE

    BEGIN

    PRINT ' * Detected setting for ''xp_regwrite'' --> Security Audit PASSED'

    SET @TestResultCounter = @TestResultCounter + 1

    PRINT ''

    END

    SELECT @AuditLvltxt = CASE

    WHEN @AuditLevel = 0 THEN 'None'

    WHEN @AuditLevel = 1 THEN 'Successful logins only'

    WHEN @AuditLevel = 2 THEN 'Failed logins only'

    WHEN @AuditLevel = 3 THEN 'Both successful and failed logins'

    ELSE 'Unknown'

    END

    IF @AuditLevel <> 3

    BEGIN

    PRINT ' * Detected setting for ''Audit Level'' is set to: ' +@AuditLvltxt+ ' --> Security Audit FAILED --> Apply Recommended changes! ***'

    PRINT ''

    PRINT ' Reason: Logging successful and failed logins provides key information that can be used to detect\confirm password '

    PRINT ' guessing attacks. Further, logging successful login attempts can be used to confirm server access during forensic '

    PRINT ' investigations. Set logs both successful and failed login SQL Server authentication attempts. '

    PRINT ''

    PRINT ' Recommended changes: Perform the following steps to change the audit level:'

    PRINT ''

    PRINT' 1. Open SQL Server Management Studio. '

    PRINT' 2. Right click the target instance and select Properties and navigate to the Security tab. '

    PRINT' 3. Select the option Both failed and successful logins under the "Login Auditing" section and click OK. '

    PRINT' 4. Restart the SQL Server instance.'

    PRINT ''

    PRINT' Recommended changes: Made with script below'

    PRINT''

    PRINT' EXEC xp_instance_regwrite N''HKEY_LOCAL_MACHINE'', '

    PRINT' N''Software\Microsoft\MSSQLServer\MSSQLServer'', '

    PRINT' N''AuditLevel'', REG_DWORD, 3 -- Failed & Successful'

    PRINT' GO'

    PRINT''

    PRINT' By default, only failed login attempted are captured. '

    PRINT ''

    PRINT' References: '

    PRINT ' 1. https://technet.microsoft.com/en-us/library/ms188470(v=sql.120).aspx '

    PRINT ' 2. https://technet.microsoft.com/en-us/library/ms188470(v=sql.110).aspx '

    PRINT''

    PRINT ' Note: A value of ''all'' indicates a server login auditing setting of ''Both failed and successful logins''. '

    PRINT ''

    INSERT INTO #SASATFailed SELECT 'Audit Level Logging'

    END

    ELSE

    BEGIN

    PRINT ' * Detected setting for ''Audit Level'' --> Security Audit PASSED'

    SET @TestResultCounter = @TestResultCounter + 1

    END

    IF (SELECT CONVERT(char(30), SERVERPROPERTY('ISIntegratedSecurityOnly'))) = 0

    BEGIN

    SET @ISIntegratedSecurityOnly = 'SQL Authentication and Windows Authentication Mode '

    PRINT ''

    PRINT ' * Detected setting for ''Server Authentication'' is set to '+@ISIntegratedSecurityOnly+' --> Security Audit FAILED --> Apply Recommended changes! ***'

    PRINT ''

    PRINT ' Reason: Windows provides a better authentication mechanism than SQL Server authentication. '

    PRINT ' A config value of Windows NT Authentication indicates the Server Authentication property is set to '

    PRINT ' Windows Authentication mode. Use Windows Authentication to validate connections. '

    PRINT ''

    PRINT ' Recommended changes: '

    PRINT ''

    PRINT ' Perform the following steps: '

    PRINT ' 1. Open SQL Server Management Studio. '

    PRINT ' 2. Open the Object Explorer tab and connect to the target database instance. '

    PRINT ' 3. Right click the instance name and select Properties. '

    PRINT ' 4. Select the Security page from the left menu. '

    PRINT ' 5. Set the Server authentication setting to Windows Authentication mode. '

    PRINT ''

    PRINT ' Default Value: Windows Authentication Mode '

    PRINT ''

    PRINT ' References: '

    PRINT ' 1. https://msdn.microsoft.com/en-us/library/ms188470(v=sql.120).aspx '

    PRINT ' 2. https://msdn.microsoft.com/en-us/library/ms188470(v=sql.110).aspx '

    PRINT ''

    INSERT INTO #SASATFailed SELECT 'Authentication Mode'

    END

    ELSE

    BEGIN

    SET @ISIntegratedSecurityOnly = 'Windows Authentication Security Mode'

    PRINT ''

    PRINT ' * Detected setting for ''Server Authentication'' is set to '+@ISIntegratedSecurityOnly+' --> Security Audit PASSED'

    PRINT ''

    SET @TestResultCounter = @TestResultCounter + 1;

    END

    DECLARE @AutoClose VARCHAR(5)

    SET @AutoClose = (SELECT COUNT(*) FROM sys.databases WHERE containment <> 0 and is_auto_close_on = 1);

    IF (SELECT @AutoClose) > 0

    BEGIN

    PRINT ' * Detected setting for ''AUTO_CLOSE OFF'' --> Security Audit FAILED --> Apply Recommended changes! ***'

    PRINT ''

    PRINT ' Reason: The AUTO_CLOSE setting on contained databases determines if an given database is closed or open after a'

    PRINT ' connection(s) is terminated. If this setting is enabled, additional connection to the database will'

    PRINT ' require the database to be reopened and procedure caches will be rebuilt'

    PRINT ' Without this setting, Denial of Serice (DoS) could occur'

    PRINT ''

    PRINT ' Recommended changes: '

    PRINT ''

    PRINT ' The following can be executed to change the value of AUTO_CLOSE to OFF. Replace the <database_name> which database'

    PRINT ' in question.'

    PRINT ''

    PRINT ' ALTER DATABASE <database_name> SET AUTO_CLOSE OFF;'

    PRINT ''

    PRINT ' Note: Server logins within the sysadmin role will retain use of this procedure. Default value is set to OFF '

    PRINT ''

    PRINT' References: '

    PRINT ' 1. https://msdn.microsoft.com/en-us/library/ff929055(v=sql.120).aspx'

    PRINT ' 2. https://msdn.microsoft.com/en-us/library/ff929055(v=sql.110).aspx'

    PRINT ''

    DECLARE @ContainName NVARCHAR(50)

    DECLARE ACOn CURSOR LOCAL FAST_FORWARD FOR (SELECT [name] FROM sys.databases WHERE containment <> 0 and is_auto_close_on = 1)

    OPEN ACOn

    FETCH NEXT FROM ACOn INTO @ContainName

    WHILE @@FETCH_STATUS = 0

    BEGIN

    PRINT' ** Contained Database with Auto Close On: '+@ContainName

    FETCH NEXT FROM ACOn INTO @ContainName

    END

    CLOSE ACOn

    DEALLOCATE ACOn

    PRINT ''

    INSERT INTO #SASATFailed SELECT 'AUTO_CLOSE OFF on Contained Database'

    END

    ELSE

    BEGIN

    PRINT ' * Detected setting for ''AUTO_CLOSE ON on Contained Database'' --> Security Audit PASSED'

    SET @TestResultCounter = @TestResultCounter + 1

    PRINT ''

    END

    CREATE TABLE #CONNECTRevokeGuest

    ([DBName] NVARCHAR(30), [name] NVARCHAR(30), [permission_name] NVARCHAR(30));

    EXECUTE sp_msforeachdb 'USE ?

    IF DB_NAME() NOT IN(''master'',''tempdb'',''model'',''msdb'')

    BEGIN

    SELECT DB_NAME() AS DBName, dpr.name, dpe.permission_name

    INTO #CONNECTRevokeGuest

    FROM sys.database_permissions dpe

    JOIN sys.database_principals dpr

    ON dpe.grantee_principal_id=dpr.principal_id

    WHERE dpr.name=''guest''

    AND dpe.permission_name=''CONNECT'';

    END'

    IF (SELECT COUNT(*) FROM #CONNECTRevokeGuest) > 0

    BEGIN

    PRINT ' * Detected setting for ''Revoke CONNECT permissions on the Guest user'' --> Security Audit FAILED --> Apply Recommended changes! ***'

    PRINT ''

    PRINT ' Reason: A login can assumes the identity of the guest user account. Revoking the connect permission'

    PRINT ' for the guest user will ensure that a login is not able to access database information without explicit access.'

    PRINT ''

    PRINT ' Recommended changes: '

    PRINT ' '

    PRINT ' The following can be executed to revoke connect permissions on the Guest user in the database'

    PRINT ' Use this script for each database as needed. Change the [database name] to your database '

    PRINT ''

    PRINT ' USE [database_name]; '

    PRINT ' GO'

    PRINT ' REVOKE CONNECT FROM guest;'

    PRINT ' GO'

    PRINT ''

    PRINT' References: '

    PRINT ' 1. https://msdn.microsoft.com/en-us/library/bb402861(v=sql.120).aspx'

    PRINT ' 2. https://msdn.microsoft.com/en-us/library/bb402861(v=sql.110).aspx'

    PRINT ''

    DECLARE @DBName1 NVARCHAR(30)

    ,@UserName1 NVARCHAR(30)

    ,@permission_name1 NVARCHAR(10)

    DECLARE RevokeGuest CURSOR LOCAL FAST_FORWARD FOR (SELECT [DBName], [name], [permission_name] FROM #CONNECTRevokeGuest)

    OPEN RevokeGuest

    FETCH NEXT FROM RevokeGuest INTO @DBName1, @UserName1, @permission_name1

    WHILE @@FETCH_STATUS = 0

    BEGIN

    PRINT' ** Check the following - Database Name: '+ @DBName1+' User Name: '+@UserName1+' Permission Granted: '+@permission_name1

    FETCH NEXT FROM RevokeGuest INTO @DBName1, @UserName1, @permission_name1

    END

    CLOSE RevokeGuest

    DEALLOCATE RevokeGuest

    PRINT''

    INSERT INTO #SASATFailed SELECT 'Revoke CONNECT permissions on the Guest user'

    END

    ELSE

    BEGIN

    PRINT ' * Detected setting for ''Revoke CONNECT permissions on the Guest user'' --> Security Audit PASSED'

    SET @TestResultCounter = @TestResultCounter + 1

    PRINT ''

    END

    CREATE TABLE #OrphanUserLIst (

    DBName NVARCHAR(50),

    name NVARCHAR(50)

    )

    EXECUTE sp_msforeachdb 'USE ?

    IF DB_NAME() NOT IN (''master'',''tempdb'',''model'',''msdb'')

    BEGIN

    INSERT INTO #OrphanUserLIst (DBName, name)

    SELECT ''?''AS DBName,

    users.name AS name

    FROM master..syslogins logins

    RIGHT JOIN sysusers users

    ON logins.sid = users.sid

    WHERE logins.sid is null

    AND issqlrole <> 1

    AND isapprole <> 1

    AND users.name <> ''INFORMATION_SCHEMA''

    AND users.name <> ''NT AUTHORITY\NETWORK SERVICE''

    AND users.name NOT IN (''guest'', ''dbo'', ''sys'')

    END'

    IF (SELECT COUNT(*) FROM #OrphanUserLIst) > 0

    BEGIN

    PRINT ' * Detected setting for Orphaned Users --> Security Audit FAILED --> Apply Recommended changes! ***'

    PRINT ''

    PRINT ' Reason: A database user which exists in the database but not on SQL Server itself cannot log in to'

    PRINT ' the instance and is known as an orphaned account. This account should be removed.'

    PRINT ' Removing orphan account will minimize potential misuse.'

    PRINT ''

    PRINT ' Recommended changes: '

    PRINT ' '

    PRINT ' The following can be used for the database in question to remove an orphan user(s) account. Replace ''database name'' with your'

    PRINT ' database name and the ''<user_name>'' with the orphan user account name.'

    PRINT ''

    PRINT ' USE [database_name]; '

    PRINT ' GO'

    PRINT ' DROP USER <user_name>;'

    PRINT ' GO'

    PRINT ''

    PRINT' References: '

    PRINT ' 1. https://msdn.microsoft.com/en-us/library/ms175475(v=sql.120).aspx'

    PRINT ' 2. https://msdn.microsoft.com/en-us/library/ms175475(v=sql.110).aspx'

    PRINT ''

    DECLARE @DBName2 NVARCHAR(50)

    ,@UserName2 NVARCHAR(30)

    DECLARE OrphanUsers CURSOR LOCAL FAST_FORWARD FOR (SELECT DBName, Name FROM #OrphanUserLIst)

    OPEN OrphanUsers

    FETCH NEXT FROM OrphanUsers INTO @DBName2, @UserName2

    WHILE @@FETCH_STATUS = 0

    BEGIN

    PRINT' ** Check the following - Database Name: '+ @DBName2+' Orphan User Name: '+@UserName2

    FETCH NEXT FROM OrphanUsers INTO @DBName2, @UserName2

    END

    CLOSE OrphanUsers

    DEALLOCATE OrphanUsers

    PRINT ''

    INSERT INTO #SASATFailed SELECT 'Drop Orphan Users'

    END

    ELSE

    BEGIN

    PRINT ' * Detected setting for Orphaned Users --> Security Audit PASSED'

    SET @TestResultCounter = @TestResultCounter + 1

    PRINT ''

    END

    CREATE TABLE #SQLAuthCD (

    DBName NVARCHAR(50),

    name NVARCHAR(50),

    type_desc NVARCHAR(10),

    name2 NVARCHAR(50))

    EXECUTE sp_msforeachdb 'USE ?

    IF DB_NAME() NOT IN (''master'',''tempdb'',''model'',''msdb'')

    BEGIN

    INSERT INTO #SQLAuthCD (DBName, name, type_desc, name2) select

    ''?''AS DBName,

    sys.database_principals.[name],

    sys.database_principals.[type_desc],

    sys.databases.[name] as Name2

    from sys.database_principals, sys.databases

    WHERE

    sys.database_principals.[name] NOT IN (''dbo'',''Information_Schema'',''sys'',''guest'',''##MS_PolicyEventProcessingLogin##'')

    AND type IN (''U'',''S'',''G'')

    AND sys.database_principals.[type_desc] = ''SQL_USER''

    AND sys.databases.containment <> 0

    AND ''?'' = sys.databases.[name]

    END'

    IF (SELECT COUNT(*) FROM #SQLAuthCD) > 0

    BEGIN

    PRINT ' * Detected setting for SQL Authentication in Contained Databases --> Security Audit FAILED --> Apply Recommended changes! ***'

    PRINT ''

    PRINT ' Reason: Password complexity rules are not enforced in contained databases. Without an enforced password policy, there is an'

    PRINT ' increase in the likelihood of a weak credential being established in a contained database.'

    PRINT ''

    PRINT ' Recommended changes: Change the user account to use Windows Authentication'

    PRINT ''

    PRINT' References: '

    PRINT ' 1. https://msdn.microsoft.com/en-us/library/ff929055(v=sql.120).aspx'

    PRINT ''

    DECLARE @DBName3 NVARCHAR(30)

    ,@UserName3 NVARCHAR(30)

    ,@type_desc3 NVARCHAR(10)

    DECLARE SQLAuthContain CURSOR LOCAL FAST_FORWARD FOR (SELECT DBName, name, type_desc FROM #SQLAuthCD)

    OPEN SQLAuthContain

    FETCH NEXT FROM SQLAuthContain INTO @DBName3, @UserName3, @type_desc3

    WHILE @@FETCH_STATUS = 0

    BEGIN

    PRINT' ** Check the following - Database Name: '+ @DBName3+' User Name: '+@UserName3+' Type of Login: '+@type_desc3

    FETCH NEXT FROM SQLAuthContain INTO @DBName3, @UserName3, @type_desc3

    END

    CLOSE SQLAuthContain

    DEALLOCATE SQLAuthContain

    PRINT ''

    INSERT INTO #SASATFailed SELECT 'SQL Authentication in Contained Databases'

    END

    ELSE

    BEGIN

    PRINT ' * Detected setting for SQL Authentication in Contained Databases --> Security Audit PASSED'

    SET @TestResultCounter = @TestResultCounter + 1

    PRINT ''

    END

    CREATE TABLE #CHECK_EXPIRATION

    ([SQLLoginName] NVARCHAR(50));

    INSERT INTO #CHECK_EXPIRATION SELECT SQLLoginName = sp.name

    FROM sys.server_principals sp JOIN sys.sql_logins AS sl ON sl.principal_id = sp.principal_id

    WHERE sp.type_desc = 'SQL_LOGIN'

    AND sp.name in (SELECT name AS IsSysAdmin FROM sys.server_principals p

    WHERE IS_SRVROLEMEMBER('sysadmin',name) = 1)

    AND sl.is_expiration_checked <> 1

    IF (SELECT COUNT(*) FROM #CHECK_EXPIRATION) > 0

    BEGIN

    PRINT ' * Detected setting for ''CHECK_EXPIRATION'' Option to ON for All SQL Authenticated Logins Within the Sysadmin --> Security Audit FAILED --> Apply Recommended changes! ***'

    PRINT ''

    PRINT ' Reason: Ensuring SQL logins comply with the secure password policy applied by the Windows Server Benchmark will ensure'

    PRINT ' the passwords for SQL logins with Sysadmin privileges are changed on a frequent basis to help prevent'

    PRINT ' compromise via a brute force attack.'

    PRINT ''

    PRINT ' Recommended changes: '

    PRINT ' '

    PRINT ' The following can be executed to change the CHECK_EXPIRATION setting to ON'

    PRINT ''

    PRINT ' USE [MASTER]; '

    PRINT ' GO'

    PRINT ' ALTER LOGIN [login_name] WITH CHECK_EXPIRATION = ON;'

    PRINT ' GO'

    PRINT ''

    PRINT ' Default Value: ON (enabled).'

    PRINT ''

    PRINT' References: '

    PRINT ' 1. https://msdn.microsoft.com/en-us/library/ms161959(v=sql.120).aspx'

    PRINT ' 2. https://msdn.microsoft.com/en-us/library/ms161959(v=sql.110).aspx'

    PRINT ''

    DECLARE @UserName4 NVARCHAR(30)

    DECLARE CHECKEXPIRATION CURSOR LOCAL FAST_FORWARD FOR (SELECT [SQLLoginName] FROM #CHECK_EXPIRATION)

    OPEN CHECKEXPIRATION

    FETCH NEXT FROM CHECKEXPIRATION INTO @UserName4

    WHILE @@FETCH_STATUS = 0

    BEGIN

    PRINT' ** Check the following - User Name: '+@UserName4

    FETCH NEXT FROM CHECKEXPIRATION INTO @UserName4

    END

    CLOSE CHECKEXPIRATION

    DEALLOCATE CHECKEXPIRATION

    PRINT ''

    INSERT INTO #SASATFailed SELECT 'CHECK_EXPIRATION SQL Authenticated Logins sysadmin'

    END

    ELSE

    BEGIN

    PRINT ' * Detected setting for ''CHECK_EXPIRATION'' for All SQL Authenticated Logins Within the Sysadmin --> Security Audit PASSED'

    SET @TestResultCounter = @TestResultCounter + 1

    PRINT ''

    END

    CREATE TABLE #CHECK_POLICY

    ([SQLLoginName] NVARCHAR(50), [PasswordPolicyEnforced] NVARCHAR(2));

    INSERT INTO #CHECK_POLICY SELECT SQLLoginName = sp.name, PasswordPolicyEnforced = CAST(sl.is_policy_checked AS BIT)

    FROM sys.server_principals sp JOIN sys.sql_logins AS sl ON sl.principal_id = sp.principal_id

    WHERE sp.type_desc = 'SQL_LOGIN'

    AND CAST(sl.is_policy_checked AS BIT) = 0;

    IF (SELECT COUNT(*) FROM #CHECK_POLICY) > 0

    BEGIN

    PRINT ' * Detected setting for ''CHECK_POLICY'' Option to ON for All SQL Authenticated Logins --> Security Audit FAILED --> Apply Recommended changes! ***'

    PRINT ''

    PRINT ' Reason: By ensuring SQL logins comply with the secure password policy the SQL logins will not have blank passwords'

    PRINT ' and cannot be easily compromised from a brute force attack.'

    PRINT ''

    PRINT ' Recommended changes: '

    PRINT ' '

    PRINT ' The following can be executed to change the CHECK_POLICY setting to ON'

    PRINT ''

    PRINT ' USE [MASTER]; '

    PRINT ' GO'

    PRINT ' ALTER LOGIN [login_name] WITH CHECK_POLICY = ON;'

    PRINT ' GO'

    PRINT ''

    PRINT ' Default Value: ON (enabled).'

    PRINT ''

    PRINT' References: '

    PRINT ' 1. https://msdn.microsoft.com/en-us/library/ms161959(v=sql.120).aspx'

    PRINT ' 2. https://msdn.microsoft.com/en-us/library/ms161959(v=sql.110).aspx'

    PRINT ''

    DECLARE @UserName5 NVARCHAR(30)

    DECLARE CHECKPOLICY CURSOR LOCAL FAST_FORWARD FOR (SELECT [SQLLoginName] FROM #CHECK_POLICY)

    OPEN CHECKPOLICY

    FETCH NEXT FROM CHECKPOLICY INTO @UserName5

    WHILE @@FETCH_STATUS = 0

    BEGIN

    PRINT' ** Check the following - SQL User Name: '+@UserName5

    FETCH NEXT FROM CHECKPOLICY INTO @UserName5

    END

    CLOSE CHECKPOLICY

    DEALLOCATE CHECKPOLICY

    PRINT ''

    INSERT INTO #SASATFailed SELECT 'CHECK_POLICY for All SQL Authenticated Logins'

    END

    ELSE

    BEGIN

    PRINT ' * Detected setting for ''CHECK_POLICY'' Option to ON for All SQL Authenticated Logins --> Security Audit PASSED'

    SET @TestResultCounter = @TestResultCounter + 1

    PRINT ''

    END

    CREATE TABLE #CLRAssemblyPermission

    ([Name] NVARCHAR(50), [permission_set_desc] NVARCHAR(30));

    INSERT INTO #CLRAssemblyPermission

    SELECT name AS Name, permission_set_desc AS 'Permission Set Description'

    FROM sys.assemblies WHERE is_user_defined = 1;

    IF (SELECT COUNT(*) FROM #CLRAssemblyPermission) > 0

    BEGIN

    PRINT ' * Detected setting for ''CLR Assembly Permission Set'' to SAFE_ACCESS for All CLR Assemblies --> Security Audit FAILED --> Apply Recommended changes! ***'

    PRINT ''

    PRINT ' Reason: By setting CLR Assembly Permission Sets to SAFE_ACCESS will prevent assemblies from accessing external'

    PRINT ' resources such as the registry, network resources, files and environment variables.'

    PRINT ' Assemblies with EXTERNAL_ACCESS / UNSAFE permission sets can be used to access areas of the operating system,'

    PRINT ' steal/transmit data and/or alter the state of other protection measures of like antivirus.'

    PRINT ''

    PRINT ' Recommended changes: '

    PRINT ' '

    PRINT ' The following can be executed to change the CHECK_POLICY setting to ON'

    PRINT ''

    PRINT ' USE [MASTER]; '

    PRINT ' GO'

    PRINT ' ALTER ASSEMBLY assembly_name WITH PERMISSION_SET = SAFE;'

    PRINT ' GO'

    PRINT ''

    PRINT ' Default Value: is SAFE permission.'

    PRINT ''

    PRINT' References: '

    PRINT ' 1. https://msdn.microsoft.com/en-us/library/ms345101(v=sql.120).aspx'

    PRINT ' 2. https://msdn.microsoft.com/en-us/library/ms345101(v=sql.110).aspx'

    PRINT ' 3. https://msdn.microsoft.com/en-us/library/ms189790(v=sql.110).aspx'

    PRINT ' 4. https://msdn.microsoft.com/en-us/library/ms186711(v=sql.110).aspx'

    PRINT ''

    DECLARE @UserName6 NVARCHAR(30)

    ,@permission_desc NVARCHAR(10)

    DECLARE CLRAssemblyPerm CURSOR LOCAL FAST_FORWARD FOR (SELECT [Name], [permission_set_desc] FROM #CLRAssemblyPermission)

    OPEN CLRAssemblyPerm

    FETCH NEXT FROM CLRAssemblyPerm INTO @UserName6, @permission_desc

    WHILE @@FETCH_STATUS = 0

    BEGIN

    PRINT' ** Check the following - User Name: '+@UserName6+' CLR Assembly Permission: '+@permission_desc

    FETCH NEXT FROM CLRAssemblyPerm INTO @UserName6, @permission_desc

    END

    CLOSE CLRAssemblyPerm

    DEALLOCATE CLRAssemblyPerm

    PRINT ''

    INSERT INTO #SASATFailed SELECT 'CLR Assembly Permission Set'

    END

    ELSE

    BEGIN

    PRINT ' * Detected setting for ''CLR Assembly Permission Set'' to SAFE_ACCESS for All CLR Assemblies --> Security Audit PASSED'

    SET @TestResultCounter = @TestResultCounter + 1

    PRINT ''

    END

    -----------------------------------------------------------------------------

    DECLARE @AutoShrink VARCHAR(5)

    SET @AutoShrink = (SELECT COUNT(*) FROM master.dbo.sysdatabases WHERE DATABASEPROPERTYEX([Name],'IsAutoShrink') = 1);

    IF (SELECT @AutoShrink) > 0

    BEGIN

    PRINT ' * Detected setting for ''AUTO_SHIRNK OFF'' --> Security Audit FAILED --> Apply Recommended changes! ***'

    PRINT ''

    PRINT ' Reason: When you enable this option for a database, this database becomes eligible for shrinking by a'

    PRINT ' background task. This background task evaluates all databases which satisfy the criteria for Shrinking'

    PRINT ' and shrink the data or log files'

    PRINT ''

    PRINT ' This setting can cause performance probelem as listed by Microsoft. See below'

    PRINT ''

    PRINT ' 1.If multiple databases undergo frequent shrink and grow operations, then this will easily lead to file '

    PRINT ' system level fragmentation.'

    PRINT ' 2.After AUTO_SHRINK successfully shrinks the data or log file, a subsequent DML or DDL operation can slow'

    PRINT ' down significantly if space is required and the files need to grow.'

    PRINT ' 3.The AUTO_SHRINK background task can take up resources when there are a lot of databases that need shrinking.'

    PRINT ' 4.The AUTO_SHRINK background task will need to acquire locks and other synchronization which can conflict with'

    PRINT ' other regular application activity.'

    PRINT ''

    PRINT ' Recommended changes: '

    PRINT ''

    PRINT ' The following can be executed to change the value of AUTO_SHRINKE to OFF. Replace the <database_name> which database'

    PRINT ' in question.'

    PRINT ''

    PRINT ' ALTER DATABASE <databasename> SET AUTO_SHRINK OFF;'

    PRINT ''

    PRINT ' Note: Server logins within the sysadmin role will retain use of this procedure. Default value is set to OFF '

    PRINT ''

    PRINT' References: '

    PRINT ' 1. https://blogs.msdn.microsoft.com/sqlserverstorageengine/2007/03/28/turn-auto_shrink-off'

    PRINT ' 2. https://support.microsoft.com/en-ca/help/2160663/recommendations-and-guidelines-for-setting-the-auto-shrink-database-op'

    PRINT ''

    DECLARE @ShrinkName NVARCHAR(50)

    DECLARE ASkrinkOn CURSOR LOCAL FAST_FORWARD FOR (SELECT [name] FROM master.dbo.sysdatabases WHERE DATABASEPROPERTYEX([Name],'IsAutoShrink') = 1)

    OPEN ASkrinkOn

    FETCH NEXT FROM ASkrinkOn INTO @ShrinkName

    WHILE @@FETCH_STATUS = 0

    BEGIN

    PRINT' ** Database with AUTO_SHRINK On: '+@ShrinkName

    FETCH NEXT FROM ASkrinkOn INTO @ShrinkName

    END

    CLOSE ASkrinkOn

    DEALLOCATE ASkrinkOn

    PRINT ''

    INSERT INTO #SASATFailed SELECT 'AUTO_SHRINK ON on Database'

    END

    ELSE

    BEGIN

    PRINT ' * Detected setting for ''AUTO_SHRINK OFF on Contained Database'' --> Security Audit PASSED'

    SET @TestResultCounter = @TestResultCounter + 1

    PRINT ''

    END

    ----------------------------------------------------------------------------------------------------------------------

    DECLARE @checksum VARCHAR(5)

    SET @checksum = (SELECT COUNT(*) FROM sys.databases where page_verify_option_desc = 'NONE');

    IF (SELECT @checksum) > 0

    BEGIN

    PRINT ' * Detected setting for ''CHECKSUM'' --> Security Audit FAILED --> Apply Recommended changes! ***'

    PRINT ''

    PRINT ' Reason: When CHECKSUM is enabled for the PAGE_VERIFY database option, the SQL Server Database Engine '

    PRINT ' calculates a checksum over the contents of the whole page, and stores the value in the page header when a'

    PRINT ' page is written to disk. When the page is read from disk, the checksum is recomputed and compared to the'

    PRINT ' checksum value that is stored in the page header. This helps provide a high level of data-file integrity.'

    PRINT ''

    PRINT ' Recommended changes: '

    PRINT ''

    PRINT ' The following can be executed to change the value of PAGE_VERIFY database option to CHECKSUM.'

    PRINT ' Replace the <database_name> which database in question.'

    PRINT ''

    PRINT ' ALTER DATABASE [databasename] SET PAGE_VERIFY CHECKSUM WITH NO_WAIT;'

    PRINT ''

    PRINT ' Note: Server logins within the sysadmin role will retain use of this procedure. Default value is set to CHECKSUM '

    PRINT ''

    PRINT' References: '

    PRINT ' 1. https://docs.microsoft.com/en-us/sql/relational-databases/policy-based-management/set-the-page-verify-database-option-to-checksum'

    PRINT ''

    DECLARE @CheckSumName NVARCHAR(50)

    DECLARE CheckSumOFF CURSOR LOCAL FAST_FORWARD FOR (SELECT [name] FROM sys.databases where page_verify_option_desc = 'NONE')

    OPEN CheckSumOFF

    FETCH NEXT FROM CheckSumOFF INTO @CheckSumName

    WHILE @@FETCH_STATUS = 0

    BEGIN

    PRINT' ** Database with CHECKSUM OFF: '+@CheckSumName

    FETCH NEXT FROM CheckSumOFF INTO @CheckSumName

    END

    CLOSE CheckSumOFF

    DEALLOCATE CheckSumOFF

    PRINT ''

    INSERT INTO #SASATFailed SELECT 'CHECKSUM OFF on Database'

    END

    ELSE

    BEGIN

    PRINT ' * Detected setting for ''CHECKSUM ON on Database'' --> Security Audit PASSED'

    SET @TestResultCounter = @TestResultCounter + 1

    PRINT ''

    END

    ----------------------------------------------------------------------------------------------------------------------

    DECLARE @RecentBAKs VARCHAR(5)

    SET @RecentBAKs = (SELECT COUNT(*) FROM sys.databases WHERE [NAME] <> 'tempdb'

    AND [NAME] NOT IN (SELECT DISTINCT database_name FROM msdb..backupset

    WHERE backup_start_date > DATEADD(DAY,-8,GETDATE())

    AND TYPE = 'D'));

    IF (SELECT @RecentBAKs) > 0

    BEGIN

    PRINT ' * Detected setting for ''No Recent Database Backups (last 7 days)'' --> Security Audit FAILED --> Apply Recommended changes! ***'

    PRINT ''

    PRINT ' Reason: Backup is an important component of a sound disaster recovery strategy. Presence of a valid and restorable'

    PRINT ' backup is the last thing you should worry about when the moment comes to execute a real disaster recovery scenario'

    PRINT ' during an emergency downtime. Here are some best practices you can follow to ensure you have a good backup in place:'

    PRINT ''

    PRINT ' 1.Make sure you are not storing your backups in the same physical location as the database files. When your physical'

    PRINT ' drive goes bad, you should be able to use the other drive or remote location that stored the backups in order to '

    PRINT ' perform a restore. Keep in mind that you could create several logical volumes or partitions from a same physical disk'

    PRINT ' drive. Carefully study the disk partition and logical colume layouts before choosing a storage location for the backups.'

    PRINT ''

    PRINT ' 2.Make sure you have a proper backup schedule established according to the needs of the application and business'

    PRINT ' requirements. As the backups get old, the risk of data loss is higher unless you have a way to regenerate all the '

    PRINT ' data till the point of failure.'

    PRINT ''

    PRINT ' 3.Make sure to actually restore the backups on a test server and verify that you can restore with all the options and'

    PRINT ' conditions you need to use during a planned or un-planned downtime.'

    PRINT ''

    PRINT ' 4.Use the verification options provided by the backup utilities [BACKUP TSQL command, SQL Server Maintenance Plans, '

    PRINT ' your backup software or solution, etc].'

    PRINT ''

    PRINT ' 5.Use advanced features like BACKUP CHECKSUM to detect problems with the backup media itself.'

    PRINT ''

    PRINT ' Recommended changes: '

    PRINT ''

    PRINT ' Using the Maintenance Wizard (under Management, Maintenance Plans) create a backup scheduled job to backup'

    PRINT ' all the databases on your SQL Server. Include all the system databases.'

    PRINT ''

    PRINT ' If you have a backup job schedules, review it to ensure the missing database is backed up'

    PRINT ''

    PRINT' References: '

    PRINT ' 1. https://support.microsoft.com/en-us/help/2027537/best-practice-recommendations-for-sql-server-database-backups'

    PRINT ''

    DECLARE @RecentBAKsName NVARCHAR(50)

    DECLARE RecentBAK CURSOR LOCAL FAST_FORWARD FOR (SELECT [NAME] FROM sys.databases WHERE [NAME] <> 'tempdb'

    AND [NAME] NOT IN (SELECT DISTINCT database_name FROM msdb..backupset

    WHERE backup_start_date > DATEADD(DAY,-8,GETDATE())

    AND TYPE = 'D' ))

    OPEN RecentBAK

    FETCH NEXT FROM RecentBAK INTO @RecentBAKsName

    WHILE @@FETCH_STATUS = 0

    BEGIN

    PRINT' ** Database with No Recent Backups: '+@RecentBAKsName

    FETCH NEXT FROM RecentBAK INTO @RecentBAKsName

    END

    CLOSE RecentBAK

    DEALLOCATE RecentBAK

    PRINT ''

    INSERT INTO #SASATFailed SELECT 'No Rcent Database Backups (last 7 days)'

    END

    ELSE

    BEGIN

    PRINT ' * Detected setting for ''Recent Database Backups'' --> Security Audit PASSED'

    SET @TestResultCounter = @TestResultCounter + 1

    PRINT ''

    END

    ----------------------------------------------------------------------------------------------------------------------

    PRINT ''

    IF @InstanceName <> 'Default Instance'

    BEGIN

    PRINT '------------------ * Detected -> SQL Server Instance - Manual Analysis Required ------------------------------------------- '

    PRINT ''

    PRINT ' Reason: Production SQL Server instances that are non-clustered should have hidden instances to prevent'

    PRINT ' the detection by individuals and cannot be enumerated. DO NOT make this change on clustered'

    PRINT ' SQL Servers as it could break the cluster itself.'

    PRINT ''

    PRINT ' Set the ''Hide Instance'' option to ''Yes'' for Production SQL Server instances that are non-clustered'

    PRINT ''

    PRINT ' Recommended changes: '

    PRINT ''

    PRINT' The following steps can be performed with SQL Server Configuration Manager: '

    PRINT ''

    PRINT ' 1. In SQL Server Configuration Manager, expand SQL Server Network Configuration, right-click'

    PRINT ' Protocols for <server instance>, and then select Properties. '

    PRINT ''

    PRINT ' 2. On the Flags tab, in the Hide Instance box, select Yes, and then click OK to close the dialog box.'

    PRINT ' The change takes effect immediately for new connections. '

    PRINT ''

    PRINT ' Default Value: SQL Server instances are show and not hidden. '

    PRINT ''

    PRINT' References: '

    PRINT ' 1. https://msdn.microsoft.com/en-us/library/ms179327(v=sql.120).aspx'

    PRINT ' 2. https://msdn.microsoft.com/en-us/library/ms179327(v=sql.110).aspx'

    PRINT ''

    INSERT INTO #SASATFailed SELECT 'Hide Instance Name'

    END

    PRINT'------------------------------------ Automated Check/Test Summary Report -------------------------------------------------------'

    PRINT ''

    SET @ResultsPercentage = (@TestResultCounter / @TotalAutomatedTests)*100

    PRINT ' Total number of automated checks/tests that have passed is '+(CONVERT(varchar(4),@TestResultCounter)) +' out of '+(CONVERT(varchar(4),@TotalAutomatedTests))+'. Success rate of '+ (CONVERT(varchar(4),@ResultsPercentage))+'%'

    PRINT ''

    PRINT ' Summary of sections that have been marked as Failed/Warning'

    PRINT ''

    DECLARE @FailedName NVARCHAR(50)

    DECLARE AuditRpt CURSOR LOCAL FAST_FORWARD FOR (SELECT AuditName FROM #SASATFailed)

    OPEN AuditRpt

    FETCH NEXT FROM AuditRpt INTO @FailedName

    WHILE @@FETCH_STATUS = 0

    BEGIN

    PRINT' - '+ @FailedName

    FETCH NEXT FROM AuditRpt INTO @FailedName

    END

    CLOSE AuditRpt

    DEALLOCATE AuditRpt

    PRINT ''

    PRINT'-------------------------------------------- End of SASAT Report ---------------------------------------------------------------'

    PRINT ''

    -- Performing clean up

    IF OBJECT_ID('tempdb..#AutoStart') IS NOT NULL

    BEGIN

    DROP TABLE #AutoStart;

    END

    IF OBJECT_ID('tempdb..#OrphanUserLIst') IS NOT NULL

    BEGIN

    DROP TABLE #OrphanUserLIst;

    END

    IF OBJECT_ID('tempdb..#SQLAuthCD') IS NOT NULL

    BEGIN

    DROP TABLE #SQLAuthCD;

    END

    IF OBJECT_ID('tempdb..#CLRAssemblyPermission') IS NOT NULL

    BEGIN

    DROP TABLE #CLRAssemblyPermission;

    END

    IF OBJECT_ID('tempdb..#CHECK_POLICY') IS NOT NULL

    BEGIN

    DROP TABLE #CHECK_POLICY;

    END

    IF OBJECT_ID('tempdb..#CHECK_EXPIRATION') IS NOT NULL

    BEGIN

    DROP TABLE #CHECK_EXPIRATION;

    END

    IF OBJECT_ID('tempdb..#CONNECTRevokeGuest') IS NOT NULL

    BEGIN

    DROP TABLE #CONNECTRevokeGuest;

    END

    IF OBJECT_ID('tempdb..#OrphanUers') IS NOT NULL

    BEGIN

    DROP TABLE #OrphanUers;

    END

    IF OBJECT_ID('tempdb..#TrustedDB') IS NOT NULL

    BEGIN

    DROP TABLE #TrustedDB;

    END

    IF OBJECT_ID('tempdb..#nodes') IS NOT NULL

    BEGIN

    DROP TABLE #nodes;

    END

    IF OBJECT_ID('tempdb..#KERBINFO') IS NOT NULL

    BEGIN

    DROP TABLE #KERBINFO;

    END

    IF OBJECT_ID('tempdb..#SysAdminAccount') IS NOT NULL

    BEGIN

    DROP TABLE #SysAdminAccount;

    END

    IF OBJECT_ID('tempdb..#SrvAdmin') IS NOT NULL

    BEGIN

    DROP TABLE #SrvAdmin;

    END

    IF OBJECT_ID('tempdb..#SQL_Server_Settings') IS NOT NULL

    BEGIN

    DROP TABLE #SQL_Server_Settings;

    END

    IF OBJECT_ID('tempdb..#TraceStats') IS NOT NULL

    BEGIN

    DROP TABLE #TraceStats;

    END

    IF OBJECT_ID('tempdb..#SASATFailed') IS NOT NULL

    BEGIN

    DROP TABLE #SASATFailed;

    END

    GO

    /*

    -- Un remark the following lines if you would like to show all config setting

    PRINT ''

    PRINT ' SQL Server Configuration Settings for this server as per SP_CONFIGURE'

    PRINT ''

    PRINT ' When making changes to SQL Server configurations, some changes are immediate and some require a restart'

    PRINT ' of the SQL related services. Below shows current configurations and when the change(s) take effect.'

    PRINT ''

    SELECT Name as 'Configuration Name'

    , CONVERT (NVARCHAR(6),[VALUE]) as 'Configured Value'

    , CONVERT (NVARCHAR(6),[VALUE_IN_USE]) as 'Value in Used'

    , CASE (CONVERT (NVARCHAR(15),[is_dynamic]))

    WHEN 0 THEN CAST('Service Restart Needed' as VARCHAR(25))

    WHEN 1 THEN CAST('Change is Immediate' as VARCHAR(25))

    END as ' Change Effect'

    , CONVERT (NVARCHAR(80),[Description]) as 'Description'

    FROM SYS.CONFIGURATIONS

    GO

    */

    /* Quick audit changes for SQL Server that you can do which revokes execution for PUBLIC

    REVOKE EXECUTE ON xp_dirtree TO PUBLIC;

    REVOKE EXECUTE ON xp_fixeddrives TO PUBLIC;

    REVOKE EXECUTE ON xp_servicecontrol TO PUBLIC;

    REVOKE EXECUTE ON xp_subdirs TO PUBLIC;

    REVOKE EXECUTE ON xp_regaddmultistring TO PUBLIC;

    REVOKE EXECUTE ON xp_regdeletekey TO PUBLIC;

    REVOKE EXECUTE ON xp_regdeletevalue TO PUBLIC;

    REVOKE EXECUTE ON xp_regenumvalues TO PUBLIC;

    REVOKE EXECUTE ON xp_regremovemultistring TO PUBLIC;

    REVOKE EXECUTE ON xp_regwrite TO PUBLIC;

    REVOKE EXECUTE ON xp_regread TO PUBLIC;

    REVOKE EXECUTE ON xp_dirtree TO PUBLIC;

    REVOKE EXECUTE ON xp_fixeddrives TO PUBLIC;

    REVOKE EXECUTE ON xp_servicecontrol TO PUBLIC;

    EXEC sp_configure 'show advanced options', 1;

    RECONFIGURE;

    */

    Rudy

  • Rudy Panigas

    SSChampion

    Points: 10695

    Hmm. I have attach the file to this post called SASAT-ver5.9.txt. Hope you can access it. If not you could search this site in the script section.

    last hope is to send me your email address and I will send it.. ugh

    Attachments:
    You must be logged in to view attached files.

    Rudy

  • mpaivant

    Newbie

    Points: 2

    Rudy Panigas, my friend, thank you worked well! Congratulations on the scripts!

  • Rudy Panigas

    SSChampion

    Points: 10695

    Your are welcome. I will be releasing a newer version in the near future. Hopefully it will be displayed correctly.

    Rudy

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply