sp_CheckSecurity.sql

IF OBJECT_ID('dbo.sp_CheckSecurity') IS NULL
  EXEC ('CREATE PROCEDURE dbo.sp_CheckSecurity AS RETURN 0;');
GO


ALTER PROCEDURE dbo.sp_CheckSecurity
	@Mode TINYINT = 99
	, @CheckLocalAdmin BIT = 0
	, @PreferredDBOwner NVARCHAR(255) = NULL
	, @Override BIT = 0
	, @Help BIT = 0
	, @VersionCheck BIT = 0

WITH RECOMPILE
AS
SET NOCOUNT ON;

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

DECLARE 
    @Version VARCHAR(10) = NULL
	, @VersionDate DATETIME = NULL

SELECT
    @Version = '2026.4.1'
    , @VersionDate = '20260420';

/* Version check */
IF @VersionCheck = 1 BEGIN

	SELECT
		@Version AS VersionNumber
		, @VersionDate AS VersionDate

	RETURN;
	END;  


/* @Help = 1 */
IF @Help = 1 BEGIN
	PRINT '
/*
    sp_CheckSecurity from https://straightpathsql.com/
    	
    This script checks your SQL Server for several dozen possible vulnerabilities
    and gives you an order list with explanations and action items.
    
    Known limitations of this version:
    - sp_CheckSecurity only works Microsoft-supported versions of SQL Server, so 
    that means SQL Server 2016 or later.
    - sp_CheckSecurity will work with SQL Server 2012 and 2014, but it will skip
    a few checks. The results should still be valid and helpful, but you should
    really considering upgrading to a newer version by now.
    - If you attempt to execute sp_CheckSecurity on SQL Server 2008 R2 or older,
    then you will only receive an output message saying this will not run on your
    version. I''m sorry. No really, I''m sorry you have to support a version of
    SQL Server that old.
    - sp_CheckSecurity is designed only for database administrators, so the user
    must be a member of the sysadmin role to complete the checks.
    - If a database name has a question mark in it, then certain checks will fail
    due to the usage of sp_MSforeachdb.
    
    Parameters:
    @Mode   0=All discovered vulnerabilities
			1=Only high vulnerability items will be shown
			99=Information and all discovered vulnerabilities shown(DEFAULT)
    
	@CheckLocalAdmin 1=Check members of local Administrators
                     0=Do NOT check members of local Administrators(DEFAULT)
    
	@PreferredDBOwner (This can be whatever login you prefer to have as the owner
                       of your databases. By default, it will be NULL and will
                       not check unless you provide a preferred owner login.)
    
	@Override for allowing checks on instances of over 50 databases


    *** WARNING FOR @CheckLocalAdmin usage ***

    If you execute sp_CheckSecurity with @CheckLocalAdmin = 1, then sp_CheckSecurity
    will attempt to read and record the members of the BUILTIN\Administrators
    group. If BUILTIN\Administrators is not currently a member of the Logins,
    then sp_CheckSecurity will proceed with the following logic.
    
    1. BEGIN an explicit transaction.
    2. Add BUILTIN\Administrators as a Login.
    3. Read and record the members of BUILTIN\Administrators.
    4. ROLLBACK the transaction, removing BUILTIN\Administrators from Logins.
    
    If you have ANY database level triggers or other fun features enabled to track 
    the addition of members to Logins then you, dear user, assume any responsibility 
    for any subsequent action from this brief addition.
    
    Please don''t say we didn''t warn you.

    *** End of WARNING FOR @CheckLocalAdmin usage ***


    MIT License
    	
    Copyright for portions of sp_CheckSecurity are held by Microsoft as part of project
    tigertoolbox and are provided under the MIT license:
    https://github.com/Microsoft/tigertoolbox
    
    Copyright for portions of sp_CheckSecurity are also held by Brent Ozar Unlimited
    as part of sp_Blitz and are provided under the MIT license:
    https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/
    	
    All other copyrights for sp_CheckSecurity are held by Straight Path Solutions.
    
    Copyright 2026 Straight Path IT Solutions, LLC
    
    Permission is hereby granted, free of charge, to any person obtaining a copy
    of this software and associated documentation files (the "Software"), to deal
    in the Software without restriction, including without limitation the rights
    to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
    copies of the Software, and to permit persons to whom the Software is
    furnished to do so, subject to the following conditions:
    
    The above copyright notice and this permission notice shall be included in all
    copies or substantial portions of the Software.
    
    THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
    IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
    FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
    AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
    LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
    OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
    SOFTWARE.

*/';
	RETURN;
	END;  

/* Check if @Override needed for too many databases */
IF @Override = 0 AND ((SELECT COUNT(database_id) from sys.databases where source_database_id IS NULL) > 50) BEGIN
	PRINT '
	You have over 50 databases, so you could have a lot of backup history.

	If you want to proceed, and you understand this procedure could use
	substantial resources to get your backup history, use @Override = 1.

	Godspeed, my friend.
'
	RETURN;
	END;  

/* check that user is in the sysadmin role */
IF IS_SRVROLEMEMBER ('sysadmin') = 0 BEGIN
	PRINT '
/*
    *** Executing user is NOT in the sysadmin role ***

    sp_CheckSecurity is designed only for database administrators, so the user
    must be a member of the sysadmin role to complete the checks.

	For more information about the limitations of sp_CheckSecurity, execute
    using @Help = 1

    *** EXECUTION ABORTED ***
    	   
*/';
	RETURN;
	END; 

DECLARE 
	@SQL NVARCHAR(4000)
	, @SQLVersion NVARCHAR(128)
	, @SQLVersionMajor DECIMAL(10,2)
	, @SQLVersionMinor DECIMAL(10,2)
	, @ComputerNamePhysicalNetBIOS NVARCHAR(128)
	, @ServerZeroName sysname
	, @SQLServerService NVARCHAR(256)
	, @SQLAgentService NVARCHAR(256)
	, @InstanceName NVARCHAR(128)
	, @Edition NVARCHAR(128);

IF OBJECT_ID('tempdb..#Results') IS NOT NULL
	DROP TABLE #Results;

CREATE TABLE #Results (
	CategoryID TINYINT
	, CheckID INT
	, [Importance] TINYINT
	, CheckName VARCHAR(50)
	, Issue NVARCHAR(MAX)
	, DatabaseName NVARCHAR(255)
	, Details NVARCHAR(MAX)
	, ActionStep NVARCHAR(MAX)
	, ReadMoreURL XML
	);

IF OBJECT_ID('tempdb..#Category') IS NOT NULL
	DROP TABLE #Category;

CREATE TABLE #Category (
    CategoryID TINYINT
	, CategoryName VARCHAR(50)
	);

INSERT #Category (CategoryID, CategoryName)
VALUES
    (1, 'Discovery')
    , (2, 'Recoverability')
    , (3, 'Security')
    , (4, 'Availability')
    , (5, 'Integrity')
    , (6, 'Reliability')
    , (7, 'Performance')
    , (8, 'Troubleshooting');

IF OBJECT_ID('tempdb..#SQLVersions') IS NOT NULL
	DROP TABLE #SQLVersions;

CREATE TABLE #SQLVersions (
	VersionName VARCHAR(10)
	, VersionNumber DECIMAL(10,2)
	);

INSERT #SQLVersions
VALUES
	('2008', 10)
	, ('2012', 11)
	, ('2014', 12)
	, ('2016', 13)
	, ('2017', 14)
	, ('2019', 15)
	, ('2022', 16)
	, ('2025', 17);

/* SQL Server version */
SELECT @SQLVersion = CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(128));

SELECT 
	@SQLVersionMajor = SUBSTRING(@SQLVersion, 1,CHARINDEX('.', @SQLVersion) + 1 )
	, @SQLVersionMinor = PARSENAME(CONVERT(varchar(32), @SQLVersion), 2);

/* check for unsupported version */	
IF @Override = 0 AND @SQLVersionMajor < 11 BEGIN
	PRINT '
/*
    *** Unsupported SQL Server Version ***

    sp_CheckSecurity is supported only for execution on SQL Server 2012 and later.

	For more information about the limitations of sp_CheckSecurity, execute
    using @Help = 1

    *** EXECUTION ABORTED ***
    	   
*/';
	RETURN;
	END; 

SELECT
	@ComputerNamePhysicalNetBIOS = CAST(SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS NVARCHAR(128))
	, @InstanceName = CAST(SERVERPROPERTY('InstanceName') AS NVARCHAR(128))
	, @Edition = CAST(SERVERPROPERTY('Edition') AS NVARCHAR(128));


SELECT @ServerZeroName = [name]
FROM sys.servers
WHERE server_id = 0;

SELECT @SQLServerService = service_account
FROM sys.dm_server_services 
WHERE servicename LIKE 'SQL Server (%';

SELECT @SQLAgentService = service_account
FROM sys.dm_server_services 
WHERE servicename LIKE 'SQL Server Agent%';

/* 
Discovery
*/
IF @Mode IN (99) BEGIN /* Collect instance info */
	/* capture date */
	INSERT #Results
	SELECT 
		1
		, 100
		, 0
		, '  Capture date'
		, 'Capture date'
		, NULL
		, '(Information captured on ' + CONVERT(VARCHAR(100), GETDATE(), 101) + ' using version ' + @Version + ')'
		, NULL
		, '';

	/* server name */
	INSERT #Results
	SELECT 
		1
		, 101
		, 0
		, '  Server Name'
		, 'Server Name'
		, NULL
		, COALESCE(@ComputerNamePhysicalNetBIOS,'')
		, NULL
		, '';

	/* instance name */
	INSERT #Results
	SELECT
		 1
		, 102
		, 0
		, ' Instance Name'
		, 'Instance Name'
		, NULL
		, COALESCE(@InstanceName, '(default instance)')
		, NULL
		, '';

	/* instance version */
	INSERT #Results
	SELECT 
		 1
		, 103
		, 0
		, ' Instance Version'
		, 'Instance Version'
		, NULL
		, 'SQL Server ' + VersionName
		, NULL
		, ''
	FROM #SQLVersions
	WHERE VersionNumber = @SQLVersionMajor;

	/* instance edition */
	INSERT #Results
	SELECT 
		 1
		, 104
		, 0
		, 'Instance Edition'
		, 'Instance Edition'
		, NULL
		, @Edition
		, NULL
		, '';

	/* instance build */
	INSERT #Results
	SELECT 
		 1
		, 105
		, 0
		, 'Instance Build'
		, 'Instance Build'
		, NULL
		, @SQLVersion
		, NULL
		, '';

	/* SQL Server service account */
	INSERT #Results
	SELECT 
		 1
		, 116
		, 0
		, 'SQL Server Service Account'
		, 'SQL Server Service Account'
		, NULL
		, service_account
		, NULL
		, ''
	FROM sys.dm_server_services 
	WHERE servicename like 'SQL Server (%';

	/* SQL Agent service account */
	INSERT #Results
	SELECT 
		 1
		, 117
		, 0
		, 'SQL Agent Service Account'
		, 'SQL Agent Service Account'
		, NULL
		, service_account
		, NULL
		, ''
	FROM sys.dm_server_services 
	WHERE servicename like 'SQL Server Agent%';

	/* IP address */
	INSERT #Results
	SELECT 
		1
		, 119
		, 0
		, 'IP address'
		, 'IP address'
		, NULL
		, COALESCE(CONVERT(VARCHAR(15), CONNECTIONPROPERTY('local_net_address')), 'UNKNOWN')
		, 'Check to make sure is not an externally-facing server and this IP address cannot be reached outside your network.'
		, 'https://straightpathsql.com/check/ip-address';

	END;


IF @Mode IN (0, 1, 99) BEGIN /* Collect issues info */

	/* remote admin connections */
	INSERT #Results
	SELECT 
		3
		, 310
		, 2
		, 'Configuration: Remote admin connections'
		, 'Remote admin connections are currently ' 
			+  CASE value_in_use
				WHEN 1 THEN 'ENABLED.'
				ELSE 'DISABLED.'
				END
		, NULL
		, 'Remote admin connections are currently ' 
			+  CASE value_in_use
				WHEN 1 THEN 'ENABLED.'
				ELSE 'DISABLED.'
				END
		, 'We recommend ''remote admin connections'' be ENABLED as a troubleshooting option for sysadmin role members.'
		, 'https://straightpathsql.com/check/remote-dedicated-admin-connections'
	FROM sys.configurations
	WHERE [name] = 'remote admin connections';


	/* Database Mail XPs */
	INSERT #Results
	SELECT 
		3
		, 311
		, 2
		, 'Configuration: Database Mail XPs'
		, 'Database Mail XPs'
		, NULL
		, 'Database Mail XPs are currently ' 
		+  CASE value_in_use
			WHEN 1 THEN 'ENABLED.'
			ELSE 'DISABLED.'
			END
		, 'Enabling Database Mail XPs can be useful, but be aware if your instance is breached they could be used to initiate a Denial Of Service attack.'
		, 'https://straightpathsql.com/check/database-mail-xps'
		FROM sys.configurations
		WHERE [name] = 'Database Mail XPs';


	/* check for supported versions */
	IF SERVERPROPERTY('EngineEdition') <> 8 /* Azure Managed Instances */ BEGIN
		IF @SQLVersionMajor < 13 BEGIN

			INSERT #Results
			SELECT 
				6
				, 602
				, 1
				, '*Unsupported version of SQL Server'
				, 'This version of SQL Server is no longer supported by Microsoft.'
				, NULL
				, 'SQL Server ' +  VersionName + ' is no longer supported, so there will be no future security updates.'
				, 'Upgrade to SQL Server 2016 or higher.'
				, 'https://straightpathsql.com/check/unsupported-versions'
			FROM #SQLVersions
			WHERE VersionNumber = @SQLVersionMajor
			END;
		END;
	

	/* check for security update */
	IF SERVERPROPERTY('EngineEdition') <> 8 /* Azure Managed Instances */ BEGIN
		IF ((@SQLVersionMajor = 11 AND @SQLVersionMinor < 7507) OR
			(@SQLVersionMajor = 12 AND @SQLVersionMinor < 6449) OR
			(@SQLVersionMajor = 13 AND @SQLVersionMinor < 6485) OR
			(@SQLVersionMajor = 14 AND @SQLVersionMinor < 3525) OR
			(@SQLVersionMajor = 15 AND @SQLVersionMinor < 4465) OR
			(@SQLVersionMajor = 16 AND @SQLVersionMinor < 4250) OR
			(@SQLVersionMajor = 17 AND @SQLVersionMinor < 4030) )

		INSERT #Results
		SELECT 
			6
			, 601
			, 1
			, '*Security update available'
			, 'There are security updates from Microsoft that have not been applied to this instance.'
			, NULL
			, 'There is at least one security update available for SQL Server ' +  VersionName + ' that has not been applied.'
			, 'Apply the most recent cumulative update or GDR for SQL Server ' +  VersionName + '.' 
			, 'https://straightpathsql.com/check/security-update'
		FROM #SQLVersions
		WHERE VersionNumber = @SQLVersionMajor
		END;


	/* check for encrypted databases */
	INSERT #Results
	SELECT 
		3
		, 312
		, 2
		, 'Encrypted database' 
		, 'One or more databases are using SQL Server encryption.' 
		, NULL
		, 'This instance has ' + CONVERT(VARCHAR(10), COUNT(database_id)) + ' encrypted databases using ' + key_algorithm + ' ' + CONVERT(VARCHAR(5), key_length) + '.'
		, 'Having encrypted databases is good, but make sure you have backed up your encryption keys to a secure location.'
		, 'https://straightpathsql.com/check/encrypted-databases'
	FROM sys.dm_database_encryption_keys
	WHERE database_id > 4
	GROUP BY 
		key_algorithm
		, key_length;

	/* check for unencrypted databases */
		INSERT #Results
	SELECT 
		3
		, 312
		, 2
		, 'Unencrypted database' 
		, 'One or more databases are not using SQL Server encryption.' 
		, NULL
		, 'This instance has ' + CONVERT(VARCHAR(10), COUNT(database_id)) + ' unencrypted databases.' 
		, 'Having unencrypted databases isn''t necessarily bad, but make sure you don''t need to have these user databases encrypted.'
		, 'https://straightpathsql.com/check/unencrypted-databases'
	FROM sys.databases d
	WHERE database_id > 4
		AND NOT EXISTS (SELECT 1 from sys.dm_database_encryption_keys dek where d.database_id = dek.database_id)
		AND d.source_database_id IS NULL; /* exclude snapshot databases */


/***** Login settings *****/

	/* sa is enabled */
	INSERT #Results
	SELECT 
		3
		, 307
		, 1
		, 'Enabled sa account'
		, 'The sa login is enabled.' 
		, NULL
		, 'The sa account is enabled for connections. Hackers commonly use the [sa] account for malicious activity since it is in the [sysadmin] role.'
		, 'Disable the sa account. Disabling only prevents sa from being used as a login for connections, as it can still own databases, jobs, etc.'
		, 'https://straightpathsql.com/check/enabled-sa-login'
	FROM sys.sql_logins
	WHERE sid = 0x01
	AND is_disabled = 0;

	/* sa has been renamed */
	INSERT #Results
	SELECT 
		3
		, 340
		, 2
		, 'Renamed sa account'
		, 'The sa login has been renamed.' 
		, NULL
		, 'The sa account is has been renamed, but the new name can be easily determined by any other login.'
		, 'Did you mean to rename this? It''s more important that this login is disabled than renamed.'
		, 'https://straightpathsql.com/check/sa-login-renamed'
	FROM sys.sql_logins
	WHERE sid = 0x01
	AND [name] <> 'sa';

	/* local Administrators group members */
	DECLARE @LocalAdmin TABLE (
		AccountName VARCHAR(1000)
		, AccountType VARCHAR(8)
		, AccountPrivilege VARCHAR(9)
		, MappedLoginName VARCHAR(1000)
		, PermissionPath VARCHAR(1000)
		);
 
	 IF @CheckLocalAdmin = 1 BEGIN
		BEGIN TRAN
			IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE [name] = 'BUILTIN\Administrators')
				CREATE LOGIN [BUILTIN\Administrators] FROM WINDOWS WITH DEFAULT_DATABASE=[master];
 
			INSERT @LocalAdmin (AccountName, AccountType, AccountPrivilege, MappedLoginName, PermissionPath)
			EXEC xp_logininfo 'BUILTIN\Administrators', 'members'
 
			ROLLBACK

		INSERT #Results
		SELECT 
			3
			, 315
			, 1
			, 'local Administrators'
			, 'Members of the local Administrators Windows group.' 
			, NULL
			, 'The ' + AccountType + ' ' + AccountName + ' is in the local Administrator group. They can add themselves to the sysadmin role and then do anything in SQL Server, including dropping databases or changing other permissions.' 
			, 'Review all users and groups in the local Administrators group to verify they require these elevated permissions.' 
			, 'https://straightpathsql.com/cs/local-administrators'
		FROM @LocalAdmin
		
		INSERT #Results
		SELECT 
			3
			, 351
			, 1
			, 'Service account in local Administrators'
			, 'The SQL Server service account is a member of the local Administrators Windows group.' 
			, NULL
			, 'The account [' + AccountName + '] is in the local Administrator group, so anyone with xp_cmdshell access can do anything on this server.' 
			, 'We recommend removing the service account from the local Administrators group to adhere to the principle of least privilege.'
			, 'https://straightpathsql.com/check/service-account-permissions'
		FROM @LocalAdmin
		WHERE AccountName = @SQLServerService;

		INSERT #Results
		SELECT 
			3
			, 351
			, 1
			, 'Service account in local Administrators'
			, 'The SQL Agent service account is a member of the local Administrators Windows group.' 
			, NULL
			, 'The account [' + AccountName + '] is in the local Administrator group, so anyone with xp_cmdshell access can do anything on this server.' 
			, 'We recommend removing the service account from the local Administrators group to adhere to the principle of least privilege.'
			, 'https://straightpathsql.com/check/service-account-permissions'
		FROM @LocalAdmin
		WHERE AccountName = @SQLAgentService;
		END;


	/* sysadmin members */
	INSERT #Results
	SELECT 
		3
		, 301
		, 1
		, 'sysadmin role members'
		, 'Members of the sysadmin role.'
		, NULL
		, 'Login [' + l.name + '] is a sysadmin. They can do anything in SQL Server, including dropping databases or changing other permissions.' 
		, 'Review the list of logins and groups in the sysadmin role to verify they require these elevated permissions.'
		, 'https://straightpathsql.com/check/sysadmin'
	FROM master.sys.syslogins l
	WHERE l.sysadmin = 1
	AND l.name <> SUSER_SNAME(0x01)
	AND l.denylogin = 0
	AND l.name NOT LIKE 'NT SERVICE\%'
	AND l.name <> 'l_certSignSmDetach'; /* Added in SQL 2016 */


	/* securityadmin members */
	INSERT #Results
	SELECT 
		3
		, 302
		, 1
		, 'securityadmin role members'
		, 'Members of the securityadmin role.'
		, NULL
		, 'Login [' + l.name + '] is a security admin. They can create other logins that do anything in SQL Server, including dropping databases or changing other permissions.' 
		, 'Review the list of logins and groups in the securityadmin role to verify they require these elevated permissions.'
		, 'https://straightpathsql.com/check/securityadmin'
	FROM master.sys.syslogins l
	WHERE l.securityadmin = 1
	AND l.name <> SUSER_SNAME(0x01)
	AND l.denylogin = 0;


	/* CONTROL SERVER permissions */
	INSERT #Results
	SELECT 
		3
		, 303
		, 1
		, 'CONTROL SERVER permissions'
		, 'Logins with CONTROL SERVER permissions.'
		, NULL
		, 'Login [' + pri.[name]+ '] has the CONTROL SERVER permission. They can do anything in SQL Server, including dropping databases or changing permissions.'
		, 'Review the list of logins and groups with CONTROL SERVER permission to verify they require these elevated permissions.'
		, 'https://straightpathsql.com/check/control-server'
	FROM sys.server_principals AS pri
	WHERE pri.[principal_id] IN (
		SELECT p.[grantee_principal_id]
		FROM sys.server_permissions AS p
		WHERE p.[state] IN ( 'G', 'W' )
		AND p.[class] = 100
		AND p.[type] = 'CL' )
		AND pri.[name] NOT LIKE '##%##';


	/* check for invalid Windows logins */
	IF OBJECT_ID('tempdb..#InvalidLogins') IS NOT NULL
		DROP TABLE #InvalidLogins;
	
	CREATE TABLE #InvalidLogins (
		LoginSID VARBINARY(85)
		, LoginName VARCHAR(256)
		);

	INSERT INTO #InvalidLogins
	EXEC sp_validatelogins;

	INSERT #Results
	SELECT 
		3
		, 304
		, 3
		, 'Invalid login with Windows Authentication'
		, 'There is a login with permissions that is no longer mapped to a valid Windows user.'
		, NULL
		, QUOTENAME(LoginName) + ' is an invalid Windows user or group that is mapped to a SQL Server principal.'
		, 'Verify the account no longer exists and carefully remove all SQL Server permissions.'
		, 'https://straightpathsql.com/check/invalid-windows-login'
	FROM #InvalidLogins;


	/* blank passwords */
	INSERT #Results
	SELECT 
		3
		, 316
		, 1
		, 'Password blank'
		, 'A SQL Server login has a blank password.'
		, NULL
		, 'Login [' + name + '] has a blank password.'
		, 'Change the password to something more secure. Logins with blank passwords are easy to hack.'
		, 'https://straightpathsql.com/check/password-vulnerabilities'
	FROM sys.sql_logins
	WHERE PWDCOMPARE('',password_hash)=1;

	/* password same as login */
	INSERT #Results
	SELECT 
		3
		, 317
		, 1
		, 'Password is same as login'
		, 'A SQL Server login has a password that is the same as the login.'
		, NULL
		, 'Login [' + name + '] has a password that is the same as the login.'
		, 'Change the password to something more secure. Logins with matching passwords are easy to hack.'
		, 'https://straightpathsql.com/check/password-vulnerabilities'
	FROM sys.sql_logins
	WHERE PWDCOMPARE(name,password_hash)=1;

	/* passwords is password */
	INSERT #Results
	SELECT 
		3
		, 318
		, 1
		, 'Password is password'
		, 'A SQL Server login has a password that is the word "password".'
		, NULL
		, 'Login [' + name + '] has a password that is the word "password".'
		, 'Change the password to this login to something more secure.'
		, 'https://straightpathsql.com/check/password-vulnerabilities'
	FROM sys.sql_logins
	WHERE PWDCOMPARE('password',password_hash)=1;


/***** Instance settings *****/

	/* CLR enabled */
	INSERT #Results
	SELECT 
		3
		, 309
		, 2
		, 'Configuration: clr enabled' 
		, 'The "clr enabled" configuration is enabled.'
		, NULL
		, 'Having the ''clr enabled'' setting enabled allows for the execution of assemblies in the context of the SQL Server account.' 
		, CASE
			WHEN @SQLVersionMajor >= 14 THEN 'Starting with SQL Server 2017, use the configuration option ''clr strict security'' instead of ''clr enabled''.'
			ELSE 'A CLR assembly created with PERMISSION_SET = SAFE may be able to access external system resources, call unmanaged code, and acquire sysadmin privileges.' 
			END
		, 'https://straightpathsql.com/check/clr-enabled'
	FROM master.sys.configurations
	WHERE [name] = 'clr enabled'
	AND value_in_use = 1
	AND NOT EXISTS (SELECT 1 FROM sys.databases WHERE [name] = 'SSISDB');


	/* xp_cmdshell enabled */
	INSERT #Results
	SELECT 
		3
		, 319
		, 2
		, 'Configuration: xp_cmdshell'  
		, 'The "xp_cmdshell" configuration is enabled.'
		, NULL
		, 'xp_cmdshell allows for the execution of operating system commands in the context of the SQL Server account by members of the sysadmin role.' 
		, 'If you do not have any code requiring xp_cmdshell, disable this configuration option.'
		, 'https://straightpathsql.com/check/xp-cmdshell'
	FROM master.sys.configurations
	WHERE [name] = 'xp_cmdshell'
	AND value_in_use = 1;

	/* Ole Automation Procedures enabled */
	INSERT #Results
	SELECT 
		3
		, 320
		, 2
		, 'Configuration: Ole Automation Procedures'  
		, 'The "Ole Automation Procedures" configuration is enabled.'
		, NULL
		, 'The ''Ole Automation Procedures'' configuration allows a call to create and execute functions in the context of SQL Server.' 
		, 'If you do not have any code requiring OLE Automation objects, disable this configuration option.'
		, 'https://straightpathsql.com/check/ole-automation-procedures'
	FROM master.sys.configurations
	WHERE [name] = 'Ole Automation Procedures'
	AND value_in_use = 1;


	/* cross-database ownership chaining */
	INSERT #Results
	SELECT 
		3
		, 321
		, 2
		, 'Configuration: Cross-database ownership chaining'  
		, 'The "cross-database ownership chaining" configuration is enabled.'
		, NULL
		, 'Cross-database ownership chaining allows database owners and members of the db_ddladmin and db_owners database roles to create objects that are owned by other users.' 
		, 'Since enabling this setting allows certain users to create objects that can potentially target objects in other databases, this configuration option should be enabled only at the database level.'
		, 'https://straightpathsql.com/check/cross-database-ownership-chaining'
	FROM master.sys.configurations
	WHERE [name] = 'cross db ownership chaining'
	AND value_in_use = 1;


	/* ad hoc distributed queries */
	INSERT #Results
	SELECT 
		3
		, 322
		, 2
		, 'Configuration: Ad Hoc Distributed Queries'
		, 'The "Ad Hoc Distributed Queries" configuration is enabled.'
		, NULL
		, 'Ad hoc distributed queries use the OPENROWSET and OPENDATASOURCE functions to connect to remote data sources that use OLE DB.' 
		, 'If a malicious user was able to utilize SQL injection, having this option enabled would allow them to read data files of their choosing.'
		, 'https://straightpathsql.com/check/ad-hoc-distributed-queries'
	FROM sys.configurations 
	WHERE [name] = 'Ad Hoc Distributed Queries'
	AND value_in_use = 1;

	/* jobs owned by users */
	INSERT #Results
	SELECT 
		6
		, 611
		, 2
		, 'SQL Agent jobs owned by users'
		, 'There are jobs that appear to be owned by user logins.'
		, NULL
		, 'Job [' + j.name + '] is owned by [' + SUSER_SNAME(j.owner_sid) + ']. If this login is disabled or not available due to Active Directory problems, the job will stop working.' 
		, 'Verify this login is the correct owner for the job. If possible, see if the job can be owned by sa.'
		, 'https://straightpathsql.com/check/sql-agent-jobs-owned-by-users'
	FROM msdb.dbo.sysjobs j
	WHERE j.enabled = 1
	AND SUSER_SNAME(j.owner_sid) <> SUSER_SNAME(0x01)
	AND SUSER_SNAME(j.owner_sid) not like '##%';

	/* stored procedures that run at startup */
	INSERT #Results
	SELECT 
		3
		, 313
		, 2
		, 'Stored procedure run at Startup' 
		, 'There is a stored procedure scheduled to run on startup.'
		, NULL
		, 'Stored procedure [master].[' + r.SPECIFIC_SCHEMA + '].[' + r.SPECIFIC_NAME + '] runs automatically when SQL Server starts up.'
		, 'Verify you and your team know exactly what this stored procedure is doing, because if not then it could pose a security risk.' 
		, 'https://straightpathsql.com/check/stored-procedures-that-run-at-startup'
	FROM master.INFORMATION_SCHEMA.ROUTINES r
	WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME), 'ExecIsStartup') = 1;

	/* jobs that run at startup */
	INSERT #Results
	SELECT 
		3
		, 314
		, 2
		, 'SQL Agent job run at Startup' 
		, 'There is a job scheduled to run on startup.'
		, NULL
		, 'Job [' + j.name + '] runs automatically when SQL Server Agent starts up.'
		, 'Verify you and your team know exactly what this job is doing, because it could pose a security risk.' 
		, 'https://straightpathsql.com/check/sql-agent-jobs-that-run-at-startup'
	FROM msdb.dbo.sysschedules s
	JOIN msdb.dbo.sysjobschedules js ON s.schedule_id = js.schedule_id
	JOIN msdb.dbo.sysjobs j ON js.job_id = j.job_id
	WHERE s.freq_type = 64
		AND s.enabled = 1
		AND j.enabled = 1;


	/* check for TDE certificate backup */
	INSERT #Results
	SELECT 
		2
		, 211
		, 1
		, 'TDE certificate never backed up'
		, 'There is a certificate used for TDE that has never been backed up.'
		, db_name(d.database_id)
		, 'The certificate ' + c.name + ' used to encrypt database ' + db_name(d.database_id) + ' has never been backed up.'
		, 'Make a backup of your current certificate and store it in a secure location in case you need to restore this encrypted database.'
		, 'https://straightpathsql.com/check/no-recent-tde-certificate-backup'
	FROM sys.certificates c 
	INNER JOIN sys.dm_database_encryption_keys d 
		ON c.thumbprint = d.encryptor_thumbprint
	WHERE c.pvt_key_last_backup_date IS NULL;

	INSERT #Results
	SELECT 
		2
		, 211
		, 2
		, 'TDE certificate not backed up recently'
		, 'There is a certificate used for TDE that has not been backed up recently.'
		, db_name(d.database_id)
		, 'The certificate ' + c.name + ' used to encrypt database ' + db_name(d.database_id) + ' has not been backed up since: ' + CAST(c.pvt_key_last_backup_date AS VARCHAR(100))
		, 'Make sure you have a recent backup of your certificate in a secure location in case you need to restore your encrypted database.'
		, 'https://straightpathsql.com/check/no-recent-tde-certificate-backup'
	FROM sys.certificates c 
	INNER JOIN sys.dm_database_encryption_keys d 
		ON c.thumbprint = d.encryptor_thumbprint
	WHERE c.pvt_key_last_backup_date <= DATEADD(dd, -90, GETDATE());

	/* check TDE certificate expiration dates */
	INSERT #Results
	SELECT 
		2
		, 213
		, 2
		, 'TDE certificate set to expire'
		, 'There is a certificate used for TDE that is set to expire.'
		, db_name(d.database_id)
		, 'The certificate ' + c.name + ' used to encrypt database ' + db_name(d.database_id) + ' is set to expire on: ' + CAST(c.expiry_date AS VARCHAR(100))
		, 'Although you will still be able to backup or restore your encrypted database with an expired certificate, these should be changed regularly like passwords.'
		, 'https://straightpathsql.com/check/tde-certificate-expiration-date'
	FROM sys.certificates c 
	INNER JOIN sys.dm_database_encryption_keys d 
		ON c.thumbprint = d.encryptor_thumbprint;


	/* check for database backup certificate backup */
	IF @SQLVersionMajor >= 12 BEGIN

		SET @SQL = '
		SELECT DISTINCT
			2
			, 210
			, 1
			, ''Database backup certificate never been backed up.''
			, ''There is a certificate used for backups that has never been backed up.''
			, b.[database_name]
			, ''The certificate '' + c.name + '' used to encrypt database backups for '' + b.[database_name] + '' has never been backed up.''
			, ''Make sure you have a recent backup of your certificate in a secure location in case you need to restore encrypted database backups.''
			, ''https://straightpathsql.com/check/missing-database-backup-certificate-backup''
		FROM sys.certificates c 
		INNER JOIN msdb.dbo.backupset b
			ON c.thumbprint = b.encryptor_thumbprint
		WHERE c.pvt_key_last_backup_date IS NULL
			AND b.encryptor_thumbprint IS NOT NULL;';

		INSERT #Results
		EXEC sp_executesql @SQL;

		SET @SQL = '
		SELECT DISTINCT
			2
			, 210
			, 2
			, ''Database backup certificate not backed up recently.''
			, ''There is a certificate used for backups that has not been backed up recently.''
			, b.[database_name]
			, ''The certificate '' + c.name + '' used to encrypt database backups for '' + b.[database_name] + '' has not been backed up since: '' + CAST(c.pvt_key_last_backup_date AS VARCHAR(100))
			, ''Make sure you have a recent backup of your certificate in a secure location in case you need to restore encrypted database backups.''
			, ''https://straightpathsql.com/check/missing-database-backup-certificate-backup''
		FROM sys.certificates c 
		INNER JOIN msdb.dbo.backupset b
			ON c.thumbprint = b.encryptor_thumbprint
		WHERE c.pvt_key_last_backup_date <= DATEADD(dd, -90, GETDATE());';

		INSERT #Results
		EXEC sp_executesql @SQL;


	/* check for database backup certificate expiration dates */
		SET @SQL = '
		SELECT DISTINCT
			2
			, 212
			, 2
			, ''Database backup certificate set to expire.''
			, ''There is a certificate used for backups that is set to expire.''
			, b.[database_name]
			, ''The certificate '' + c.name + '' used to encrypt database '' + b.[database_name] + '' is set to expire on: '' + CAST(c.expiry_date AS VARCHAR(100))
			, ''You will not be able to backup or restore your encrypted database backups with an expired certificate, so these should be changed regularly like passwords.''
			, ''https://straightpathsql.com/check/database-backup-certificate-expiration-date''
		FROM sys.certificates c 
		INNER JOIN msdb.dbo.backupset b
			ON c.thumbprint = b.encryptor_thumbprint';

		INSERT #Results
		EXEC sp_executesql @SQL;
	
		END;


	/* linked server check */
 	INSERT #Results
	SELECT 
		3
		, 323
		, 1
		, 'Linked Server configured with sa'
		, 'There is a linked Server configured with the sa login.'
		, NULL
		, 'The linked server ' + s.name + ' connects to the source ' + s.data_source + ' (' + COALESCE(s.product, s.provider) + ') using the security context of the sa login, which allows any user to perform any action on the linked server.'
		, 'Change the security configuration of the linked server to use a more secure context.'
		, 'https://straightpathsql.com/check/linked-server'
	FROM sys.servers s
	INNER JOIN sys.linked_logins l
	 ON s.server_id = l.server_id
	WHERE s.is_linked = 1
	 AND l.local_principal_id = 0
	 AND l.uses_self_credential = 0
	 AND l.remote_name = 'sa';

 	INSERT #Results
	SELECT 
		3
		, 324
		, 2
		, 'Linked Server configured with fixed login'
		, 'There is a linked Server configured with a fixed login.'
		, NULL
		, 'The linked server ' + s.name + ' connects to the source ' + s.data_source + ' (' + COALESCE(s.product, s.provider) + ') using the security context of the login: [' + l.remote_name + '].'
		, 'Check the security configuration to make sure this login is not in the sysadmin role, which would allow any user to perform any action on the linked server.'
		, 'https://straightpathsql.com/check/linked-server'
	FROM sys.servers s
	INNER JOIN sys.linked_logins l
	 ON s.server_id = l.server_id
	WHERE s.is_linked = 1
	 AND l.local_principal_id = 0
	 AND l.uses_self_credential = 0
	 AND l.remote_name IS NOT NULL
	 AND l.remote_name <> 'sa';

  	INSERT #Results
	SELECT 
		3
		, 325
		, 2
		, 'Linked Server'
		, 'There is a linked Server configured using the login''s current security context.'
		, NULL
		, 'The linked server ' + s.name + ' connects to the source ' + s.data_source + ' (' + COALESCE(s.product, s.provider) + ') using the login''s current security context.'
		, 'This security context is probably not an issue, but we note it in case this linked server is no longer needed.'
		, 'https://straightpathsql.com/check/linked-server'
	FROM sys.servers s
	INNER JOIN sys.linked_logins l
	 ON s.server_id = l.server_id
	WHERE s.is_linked = 1
	 AND l.local_principal_id = 0
	 AND l.uses_self_credential = 1
	 AND l.remote_name IS NULL;

  	INSERT #Results
	SELECT 
		3
		, 325
		, 2
		, 'Linked Server'
		, 'There is a linked Server configured without a security context.'
		, NULL
		, 'The linked server ' + s.name + ' connects to the source ' + s.data_source + ' (' + COALESCE(s.product, s.provider) + ') without a security context (denied).'
		, 'This security context is probably not an issue, but we note it in case this linked server is no longer needed.'
		, 'https://straightpathsql.com/check/linked-server'
	FROM sys.servers s
	INNER JOIN sys.linked_logins l
	 ON s.server_id = l.server_id
	WHERE s.is_linked = 1
	 AND l.local_principal_id = 0
	 AND l.uses_self_credential = 0
	 AND l.remote_name IS NULL;

  	INSERT #Results
	SELECT 
		3
		, 325
		, 2
		, 'Linked Server'
		, 'There is a linked Server configured with the security context of a remote login.'
		, NULL
		, 'The linked server ' + s.name + ' connects to the source ' + s.data_source + ' (' + COALESCE(s.product, s.provider) + ') for [' + p.name + '] using the security context of the remote name: [' + l.remote_name + '].'
		, 'Check the security configuration to make sure this login is not in the sysadmin role, which would allow any user to perform any action on the linked server.'
		, 'https://straightpathsql.com/check/linked-server'
	FROM sys.servers s
	INNER JOIN sys.linked_logins l
	 ON s.server_id = l.server_id
	INNER JOIN sys.server_principals p
		ON l.local_principal_id = p.principal_id
	WHERE s.is_linked = 1
	 AND l.local_principal_id <> 0
	 AND l.uses_self_credential = 0
	 AND l.remote_name IS NOT NULL;

  	INSERT #Results
	SELECT 
		3
		, 325
		, 2
		, 'Linked Server'
		, 'There is a linked Server configured with self credentials.'
		, NULL
		, 'The linked server ' + s.name + ' connects to the source ' + s.data_source + ' (' + COALESCE(s.product, s.provider) + ') for [' + p.name + '] using their own self credentials.'
		, 'This security context is probably not an issue, but we note it in case this linked server is no longer needed.'
		, 'https://straightpathsql.com/check/linked-server'
	FROM sys.servers s
	INNER JOIN sys.linked_logins l
	 ON s.server_id = l.server_id
	INNER JOIN sys.server_principals p
		ON l.local_principal_id = p.principal_id
	WHERE s.is_linked = 1
	 AND l.local_principal_id <> 0
	 AND l.uses_self_credential = 1;

  
	/* endpoint owner */
  	INSERT #Results
	SELECT 
		4
		, 406
		, 1
		, 'Endpoints owned by users' 
		, 'There is an endpoint owned by a user login.'
		, NULL
		, 'Endpoint ' + ep.[name] + ' is owned by ' + SUSER_NAME(ep.principal_id) + '. If the endpoint owner login is disabled or not available due to Active Directory problems, then high availability will stop working.'
		, 'Verify this is the correct owner of this endpoint, and if it is not then assign ownership to sa.'
		, 'https://straightpathsql.com/check/endpoints-owned-by-users'
	FROM sys.database_mirroring_endpoints ep
	LEFT OUTER JOIN sys.dm_server_services s
	 ON SUSER_NAME(ep.principal_id) = s.service_account
	WHERE s.service_account IS NULL AND ep.principal_id <> 1;


	/* check for audits */
  	INSERT #Results
	SELECT 
		3
		, 326
		, 2
		, 'SQL Server Audit running'
		, 'There is a SQL Server Audit running.'
		, NULL
		, [name] + ' is a SQL Server Audit that is currently running.'
		, 'Verify this audit needs to be running, and if so any output files are in a secure directory.'
		, 'https://straightpathsql.com/check/sql-server-audit'
	FROM sys.dm_server_audit_status
	WHERE status = 1
	AND [name] NOT LIKE '%SQLBeacon%'; /* for SQL Beacon */


	/* database owner is not preferred owner */
	IF @PreferredDBOwner IS NOT NULL BEGIN

		INSERT #Results
		SELECT 
			3
			, 327
			, 2
			, 'Database owner is not preferred'
			, 'The database owner is not the preferred owner.'
			, [name]
			, 'The database ' + [name]
				+ ' is owned by ' + SUSER_SNAME(owner_sid) 
			, 'Verify this is the correct owner, because if this login is disabled or not available due to Active Directory problems then database accessibility could be affected.'
			, 'https://straightpathsql.com/check/database-owner-is-not-preferred-owner'
		FROM sys.databases
		WHERE (((SUSER_SNAME(owner_sid) <> SUSER_SNAME(0x01)) AND (name IN (N'master', N'model', N'msdb', N'tempdb')))
		OR ((SUSER_SNAME(owner_sid) <> @PreferredDBOwner) AND (name NOT IN (N'master', N'model', N'msdb', N'tempdb'))))
			AND source_database_id IS NULL; /* exclude snapshot databases */

		END;

	/* database owner is member of sysadmin role */
	INSERT #Results
	SELECT 
		3
		, 328
		, 2
		, 'Database owner is a sysadmin'
		, 'The database owner is a member of the sysadmin role.'
		, d.[name]
		, 'Members of the db_owner role in the database ' + d.[name] + ' can execute commands as the ' + l.[name] + ' login that owns the database.' 
		, 'Unless you are OK with db_owner role members using EXECUTE AS OWNER to do nearly anything on this instance, we recommend changing the database owner to a login not in the sysadmin role.'
		, 'https://www.insidesql.org/blogs/andreaswolter/2014/06/sql-server-database-ownership-survey-results-recommendations'
	FROM master.sys.databases d
	INNER JOIN master.sys.syslogins l
		ON d.owner_sid = l.sid
	WHERE l.sysadmin = 1
		AND d.database_id > 4
		AND d.source_database_id IS NULL; /* exclude snapshot databases */

	/* database owner is unknown */
	INSERT #Results
	SELECT 
		3
		, 305
		, 2
		, 'Database Owner is Unknown'  
		, 'The database owner is unknown (blank).'
		, [name]
		, ( 'Database name: ' + [name] + '   '
			+ 'Owner name: ' + ISNULL(SUSER_SNAME(owner_sid),'~~ UNKNOWN ~~') ) AS Details
		, 'Assign an owner to this database, preferably sa if possible.'
		, 'https://straightpathsql.com/check/database-owner-is-unknown'
	FROM sys.databases
	WHERE SUSER_SNAME(owner_sid) is NULL
		AND source_database_id IS NULL; /* exclude snapshot databases */


	/* check for SQL Server services using built-in elevated account */
	INSERT #Results
	SELECT 
		3
		, 329
		, 1
		, 'SQL Server service using built-in elevated account'
		, 'The SQL Server service is using a local elevated account.'
		, NULL
		, 'The SQL Server service is using an account that allows anyone with access to xp_cmdshell to do anything on the server.'
		, 'We recommend using a service account and not "LocalSystem" or "NT AUTHORITY\SYSTEM" for SQL Server services.'
		, 'https://straightpathsql.com/check/service-account-permissions'
	FROM sys.dm_server_services
	WHERE (service_account = 'LocalSystem' OR UPPER(service_account) = 'NT AUTHORITY\SYSTEM')
		AND servicename LIKE 'SQL Server%'
		AND servicename NOT LIKE 'SQL Server Agent%';

	INSERT #Results
	SELECT 
		3
		, 329
		, 1
		, 'SQL Agent service using built-in elevated account'
		, 'The SQL Agent service is using a local elevated account.'
		, NULL
		, 'The SQL Agent service is using an account that allows anyone with access to jobs to do anything on the server.'
		, 'We recommend using a service account and not "LocalSystem" or "NT AUTHORITY\SYSTEM" for SQL Server services.'
		, 'https://straightpathsql.com/check/service-account-permissions'
	FROM sys.dm_server_services
	WHERE (service_account = 'LocalSystem' OR UPPER(service_account) = 'NT AUTHORITY\SYSTEM')
		AND servicename LIKE 'SQL Server Agent%';


	/* check for SQL Server service account in sysadmin role */
	INSERT #Results
	SELECT 
		3
		, 350
		, 2
		, 'SQL Server service account in sysadmin'
		, 'The SQL Server service is using account [' + service_account + '], which is in the sysadmin role.'
		, NULL
		, 'The SQL Server service has permissions to do anything on the server. This could result in privilege escalation via a stored procedure or job.'
		, 'We recommend removing the service account from the sysadmin role to adhere to the principle of least privilege.'
		, 'https://straightpathsql.com/check/service-account-permissions'
	FROM sys.dm_server_services 
	WHERE servicename like 'SQL Server (%'
		AND IS_SRVROLEMEMBER ('sysadmin', service_account) = 1;

	INSERT #Results
	SELECT 
		3
		, 350
		, 2
		, 'SQL Agent service account in sysadmin'
		, 'The SQL Agent service is using account [' + service_account + '], which is in the sysadmin role.'
		, NULL
		, 'The SQL Agent service has permissions to do anything on the server. This could result in privilege escalation via a stored procedure or job.'
		, 'We recommend removing the service account from the sysadmin role to adhere to the principle of least privilege.'
		, 'https://straightpathsql.com/check/service-account-permissions'
	FROM sys.dm_server_services 
	WHERE servicename like 'SQL Server Agent%'
		AND IS_SRVROLEMEMBER ('sysadmin', service_account) = 1;


	/* Check that SQL Login Audit includes failed logins */
	DECLARE @AuditValue int = NULL;

	EXEC master.dbo.xp_instance_regread 
		@rootkey = 'HKEY_LOCAL_MACHINE'
		, @key = 'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer'
		, @value_name = 'AuditLevel'
		, @value = @AuditValue OUTPUT
		, @no_output = 'no_output';

	IF @AuditValue < 2
		INSERT #Results
		SELECT 
			3
			, 330
			, 1
			, 'No audit of failed logins'
			, 'Failed logins are not being logged.'
			, NULL
			, 'The current SQL Login Audit settings do not include failed logins.'
			, 'SQL Error logs should capture failed login for review, since these may indicate hacking attempts.'
			,'https://straightpathsql.com/check/sql-login-audit-does-not-include-failed-logins';


	/* Failed logins */
	DECLARE @ErrorLog TABLE (
		LogDate DATETIME
		, ProcessInfo NVARCHAR(50) 
		, [Text] NVARCHAR(MAX)
		)
 
	INSERT @ErrorLog
	EXEC sp_readerrorlog 0, 1, 'Login failed'

	DECLARE @FailedLogins bigint

	SET @FailedLogins = (SELECT COUNT(*) FROM @ErrorLog)

	IF @FailedLogins > 0

		INSERT #Results
		SELECT 
			3
			, 331
			, 1
			, 'Failed logins'
			, 'Recent failed logins have occurred.'
			, NULL
			, 'There have been at least ' + CONVERT(VARCHAR(10), @FailedLogins) + ' failed logins recently.'
			, 'Review the SQL Server error log for patterns of login failures or suspect IP addresses which may indicate hacking attempts.'
			,'https://straightpathsql.com/check/failed-logins';

	/* number of error logs */
	DECLARE @NumErrorLogs INT = NULL;

	EXEC master.dbo.xp_instance_regread
		@rootkey = N'HKEY_LOCAL_MACHINE'
		, @key = N'Software\Microsoft\MSSQLServer\MSSQLServer'
		, @value_name = N'NumErrorLogs'
		, @value = @NumErrorLogs OUTPUT
		, @no_output = 'no_output';

	IF (SELECT ISNULL(@NumErrorLogs, 12)) < 12 

		INSERT #Results
		SELECT 
			6
			, 622
			, 2
			, 'Too few SQL Server error log files'
			, 'Error log retention is at the default value of 6 files.'
			, NULL
			, 'This instance is configured for only ' + CONVERT(VARCHAR(10), (ISNULL(@NumErrorLogs, -1))) + ' SQL Server error log files.'
			, 'We recommend having between 12 and 52 SQL Server error log files to review for patterns of login failures or suspect IP addresses which may indicate hacking attempts.'
			, 'https://straightpathsql.com/check/number-of-sql-server-error-log-files';


	/* C2 audit mode */
	INSERT #Results
	SELECT 
		3
		, 341
		, 2
		, 'Configuration: C2 audit mode'
		, 'C2 audit mode'
		, NULL
		, 'C2 audit mode is currently ' 
		+  CASE value_in_use
			WHEN 1 THEN 'ENABLED.'
			ELSE 'DISABLED.'
			END
		, 'Enabling C2 audit mode collects failed and successful attempts to access objects, but it also saves a large amount of event information to the log file. If the data directory in which logs are being saved runs out of space, SQL Server shuts itself down.'
		, 'https://straightpathsql.com/check/c2-audit-mode'
		FROM sys.configurations
		WHERE [name] = 'c2 audit mode';


	/* common criteria compliance */
	INSERT #Results
	SELECT 
		3
		, 342
		, 2
		, 'Configuration: Common Criteria Compliance'
		, 'Common Criteria Compliance'
		, NULL
		, 'Common Criteria Compliance is currently ' 
		+  CASE value_in_use
			WHEN 1 THEN 'ENABLED.'
			ELSE 'DISABLED.'
			END
		, 'Common Criteria Compliance is an Enterprise-only feature that helps meet requirements for the Common Criteria for Information Technology Security Evaluation. It does have performance impacts, though.'
		, 'https://straightpathsql.com/check/common-criteria-compliance'
		FROM sys.configurations
		WHERE [name] = 'common criteria compliance enabled';


	/* contained database authentication */
	INSERT #Results
	SELECT 
		3
		, 343
		, 2
		, 'Configuration: Contained Database Authentication'
		, 'Contained Database Authentication'
		, NULL
		, 'Contained Database Authentication is currently ' 
		+  CASE value_in_use
			WHEN 1 THEN 'ENABLED.'
			ELSE 'DISABLED.'
			END
		, 'Contained Database Authentication is required to install contained databases. A separate check will look for any contained databases.'
		, 'https://straightpathsql.com/check/contained-database'
		FROM sys.configurations
		WHERE [name] = 'contained database authentication';


	/* remote access */
	INSERT #Results
	SELECT 
		3
		, 345
		, 2
		, 'Configuration: Remote Access'
		, 'Remote Access'
		, NULL
		, 'Remote Access is currently ' 
		+  CASE value_in_use
			WHEN 1 THEN 'ENABLED.'
			ELSE 'DISABLED.'
			END
		, 'Remote Access allows for the execution of stored procedures from remote instances. This only needs to be enabled to use linked servers and/or log shipping.'
		, 'https://straightpathsql.com/check/remote-access'
		FROM sys.configurations
		WHERE [name] = 'remote access';


	/*  Hide instance  */
	DECLARE @HideInstance INT = NULL;

	EXEC master.dbo.xp_instance_regread 
		@rootkey = 'HKEY_LOCAL_MACHINE'
		, @key = 'Software\Microsoft\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib'
		, @value_name = 'HideInstance'
		, @value = @HideInstance OUTPUT
		, @no_output = 'no_output';

	INSERT #Results
	SELECT 
		3
		, 347
		, 2
		, 'Configuration Manager: Hide Instance'
		, 'Hide Instance'
		, NULL
		, 'Hide Instance is currently ' 
		+  CASE @HideInstance
			WHEN 1 THEN 'ENABLED.'
			WHEN 0 THEN 'DISABLED.'
			ELSE 'UNKNOWN.'
			END
		, 'Hiding an instance prevents it from being seen by anyone browsing your network, however all connections must specify the port number.'
		, 'https://straightpathsql.com/check/hide-instance';


	/*  Extended protection  */
	DECLARE @ExtendedProtection INT;
	
	EXEC master.dbo.xp_instance_regread 
		'HKEY_LOCAL_MACHINE'
		, 'Software\Microsoft\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib'
		, 'ExtendedProtection'
		, @ExtendedProtection OUTPUT;

	INSERT #Results
	SELECT 
		3
		, 348
		, 2
		, 'Configuration Manager: Extended Protection'
		, 'Extended Protection'
		, NULL
		, 'Extended Protection is currently ' 
		+  CASE @ExtendedProtection
			WHEN 1 THEN 'ENABLED.'
			WHEN 0 THEN 'DISABLED.'
			ELSE 'UNKNOWN.'
			END
		, 'Extended Protection uses service binding and channel binding to help prevent an authentication relay attack.'
		, 'https://straightpathsql.com/check/extended-protection';


	/*  Force encryption  */
	DECLARE @ForceEncryption INT = NULL;

	EXEC master.dbo.xp_instance_regread 
		@rootkey = 'HKEY_LOCAL_MACHINE'
		, @key = 'Software\Microsoft\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib'
		, @value_name = 'ForceEncryption'
		, @value = @ForceEncryption OUTPUT
		, @no_output = 'no_output';


	INSERT #Results
	SELECT 
		3
		, 349
		, 2
		, 'Configuration Manager: Force Encryption'
		, 'Force Encryption'
		, NULL
		, 'Force Encryption is currently ' 
		+  CASE @ForceEncryption
			WHEN 1 THEN 'ENABLED.'
			WHEN 0 THEN 'DISABLED.'
			ELSE 'UNKNOWN.'
			END
		, 'Force Encryption ensures data in transit is encrypted, requiring all clients to use SSL/TLS encryption.'
		, 'https://straightpathsql.com/check/force-encryption';




/***** Database settings *****/

	/* contained databases */
	IF @SQLVersionMajor >= 11 BEGIN
/*
	INSERT #Results
	SELECT 
		3
		, 344
		, 2
		, 'contained database'
		, 'A contained database has certain security implications.'
		, db_name(database_id)
		, 'The database ' + db_name(database_id) + ' is set as CONTAINED.'
		, 'Although by design contained databases are isolated, be aware of other security implication of any contained database.'
		, 'https://straightpathsql.com/check/contained-database'
	FROM sys.databases
	WHERE containment > 0;
*/
		SET @SQL = '
		SELECT DISTINCT
			3
			, 344
			, 2
			, ''contained database''
			, ''The database ['' + db_name(database_id) + ''] is set as CONTAINED.''
			, db_name(database_id)
			, ''Although by design contained databases are isolated, be aware of other security implication of any contained database.''
			, ''Verify this database needs to be contained, and review the contained database users to ensure they have appropriate permissions.''
			, ''https://straightpathsql.com/check/contained-database''
			FROM sys.databases
			WHERE containment > 0;';

		INSERT #Results
		EXEC sp_executesql @SQL;

		END;

	/* database owned by Windows account */
	INSERT #Results
	SELECT 
		3
		, 346
		, 2
		, 'Database owned by Windows account'
		, 'The database [' + db_name(d.database_id) + '] is owned by the Windows account [' + sp.[name] + '].'
		, db_name(d.database_id)
		, 'Members of the db_owner role could execute queries in the context of this Windows account.'
		, 'We recommend databases be owned by disabled SQL logins with minimal permissions to prevent privilege escalation.'
		, 'https://straightpathsql.com/check/database-owner-is-not-preferred-owner'
	FROM sys.databases d
	INNER JOIN sys.server_principals sp
		ON d.owner_sid = sp.sid
	WHERE sp.[type] = 'U'
		AND d.source_database_id IS NULL; /* exclude snapshot databases */


	/* TRUSTWORTHY setting check */
	INSERT #Results
	SELECT 
		3
		, 308
		, 1
		, 'TRUSTWORTHY database owned by sysadmin'
		, 'A database with TRUSTWORTHY enabled is owned by a member of the sysadmin role.'
		, db_name(database_id)
		, 'The database ' + db_name(database_id) + ' has the TRUSTWORTHY setting enabled and is owned by a member of the sysadmin role.'
		, 'With TRUSTWORTHY setting enabled and a sysadmin database owner, any user can execute commands as a sysadmin.'
		, 'https://straightpathsql.com/check/trustworthy-database'
	FROM sys.databases
	WHERE database_id > 4
		AND is_trustworthy_on = 1
		AND IS_SRVROLEMEMBER ('sysadmin', SUSER_SNAME(owner_sid)) = 1;

	INSERT #Results
	SELECT 
		3
		, 332
		, 2
		, 'TRUSTWORTHY database'
		, 'A database has the TRUSTWORTHY configuration enabled.'
		, db_name(database_id)
		, 'The database ' + db_name(database_id) + ' has the TRUSTWORTHY setting enabled.'
		, 'With this setting ON, any code in the database to be "trusted" in usage outside the context of the database.'
		, 'https://straightpathsql.com/check/trustworthy-enabled'
	FROM sys.databases
	WHERE database_id > 4
		AND is_trustworthy_on = 1
		AND IS_SRVROLEMEMBER ('sysadmin', SUSER_SNAME(owner_sid)) = 0;


	/* db_owner role member */
	SET @SQL = 'USE [?]; 
	SELECT 3, 333, 2
	, ''db_owner role member''
	, ''db_owner role member''
	, DB_NAME()
	, (''In ['' + DB_NAME() + ''], user ['' + u.name + '']  has the role ['' + g.name + ''].  This user can perform any function in this database including changing permissions for other users.'')
	, ''Verify these elevated database permissions are required for this user.''
	, ''https://straightpathsql.com/check/db_owner-role-members''
	FROM (SELECT memberuid = convert(int, member_principal_id), groupuid = convert(int, role_principal_id) FROM [?].sys.database_role_members) m inner join [?].dbo.sysusers u on m.memberuid = u.uid inner join sysusers g on m.groupuid = g.uid where u.name <> ''dbo'' and g.name in (''db_owner'') OPTION (RECOMPILE);';

	INSERT #Results
	EXEC sp_MSforeachdb @SQL

	UPDATE #Results
	SET
		CheckID = 334
		, [Importance] = 1
		, Issue = 'db_owner role member - system databases'
		, CheckName = 'db_owner role member - system databases'
	WHERE CheckID = 333
	AND DatabaseName IN ('master','msdb');


	/* unusual database permissions */
	SET @SQL = 'USE [?]; 
	SELECT 3, 335, 2
	, ''Unusual database permissions''
	, ''Unusual database permissions''
	, DB_NAME()
	, (''In ['' + DB_NAME() + ''], user ['' + u.name + '']  has the role ['' + g.name + ''].  This is an unusual database role with elevated permissions, but it is redundant if this user is also in the db_owner role.'')
	, ''Verify these elevated database permissions are required for this user.''
	, ''https://straightpathsql.com/check/unusual-database-permissions''
	FROM (SELECT memberuid = convert(int, member_principal_id), groupuid = convert(int, role_principal_id) FROM [?].sys.database_role_members) m inner join [?].dbo.sysusers u on m.memberuid = u.uid inner join sysusers g on m.groupuid = g.uid where u.name <> ''dbo'' and g.name in (''db_accessadmin'' , ''db_securityadmin'' , ''db_ddladmin'') OPTION (RECOMPILE);';

	INSERT #Results
	EXEC sp_MSforeachdb @SQL;

	UPDATE #Results
	SET
		CheckID = 336
		, [Importance] = 1
		, Issue = 'Unusual database permissions - system databases'
		, CheckName = 'Unusual database permissions - system databases'
	WHERE CheckID = 335
	AND DatabaseName IN ('master','msdb');


	/* find roles within roles in each database */
	SET @SQL = '
	USE [?]
	IF DB_Name() NOT IN (''tempdb'') BEGIN
	SELECT 3, 337, 3
	, ''Roles within roles''
	, ''Roles within roles''
	, db_name() as [DatabaseName]
	, ''The role ['' + user_name(roles.member_principal_id) + ''] is a member of the role ['' + user_name(roles.role_principal_id)
	 + '']. Including roles in other roles can lead to unintended privilege escalation.''
	, ''Remove ['' + user_name(roles.member_principal_id) + ''] from the role ['' + user_name(roles.role_principal_id) + ''] and explicitly assign it required permissions''
	, ''https://straightpathsql.com/check/database-roles-within-roles''
	FROM sys.database_role_members AS roles, sys.database_principals users
	WHERE roles.member_principal_id = users.principal_id
	AND user_name(roles.member_principal_id) <> ''RSExecRole''
	AND ( roles.role_principal_id >= 16384 AND roles.role_principal_id <= 16393)
	AND users.type = ''R''
	END'

	INSERT #Results
	EXEC sp_MSforeachdb @SQL;


	/* find orphan user in each database */
	SET @SQL = '
	USE [?]
	IF DB_Name() NOT IN (''tempdb'') BEGIN
	SELECT 3, 338, 3
	, ''Orphaned database user''
	, ''Orphaned database user''
	, db_name() as [DatabaseName]
	, ''The database user ['' + [NAME] + ''] is orphaned, meaning it has no corresponding login at the instance level.''
	, ''Reconnect the user to an existing login using sp_change_users_login, or drop the user.''
	, ''https://straightpathsql.com/check/orphaned-users''
	FROM sys.database_principals
	WHERE sid NOT IN (SELECT sid FROM sys.server_principals)
	AND type = ''S''
	AND principal_id != 2
	AND DATALENGTH(sid) <= 28'
	+ CASE 
		WHEN @SQLVersionMajor >= 12 THEN ' AND authentication_type_desc = ''INSTANCE'''
		END
	+ ' END';

	INSERT #Results
	EXEC sp_MSforeachdb @SQL;


	/* database owner is different from owner in master */ -- has issues with mismatched collation
	SET @SQL = '
	USE [?]
	IF DB_Name() NOT IN (''tempdb'') BEGIN
	SELECT 3, 306, 3
	, ''Database owner discrepancy''
	, ''Database owner discrepancy''
	, db_name() as [DatabaseName]
	, ''The database owner ['' + dbprs.name COLLATE SQL_Latin1_General_CP1_CI_AS + ''] is different than the owner listed in master ['' + ssp.name COLLATE SQL_Latin1_General_CP1_CI_AS + ''].''
	, ''Use ALTER AUTHORIZATION ON DATABASE to set the database owner to the correct login.''
	, ''https://straightpathsql.com/check/database-owner-is-unknown''
	FROM   sys.database_principals AS dbprs
	INNER JOIN sys.databases AS dbs
	 ON dbprs.sid != dbs.owner_sid 
	JOIN sys.server_principals ssp
	 ON dbs.owner_sid = ssp.sid 
	WHERE dbs.database_id = Db_id()
	AND dbprs.principal_id = 1
	AND dbs.state_desc = ''ONLINE''
	END';

	INSERT #Results
	EXEC sp_MSforeachdb @SQL;


	/* explicit permissions granted to the Public role */
	IF @SQLVersionMajor >= 12 BEGIN
		DECLARE @DB_Name VARCHAR(256) 

		DECLARE public_cursor CURSOR FOR
			SELECT name 
			FROM master.sys.databases
			WHERE database_id > 4 
				AND state = 0
				AND source_database_id IS NULL /* exclude snapshot databases */
				AND [name] NOT IN (
					SELECT adc.database_name
					FROM sys.availability_replicas AS ar
				   JOIN sys.availability_databases_cluster adc ON adc.group_id = ar.group_id
					WHERE ar.secondary_role_allow_connections = 0
				   AND ar.replica_server_name = @@SERVERNAME
				   AND sys.fn_hadr_is_primary_replica(adc.database_name) = 0 
					)

		OPEN public_cursor 
		FETCH NEXT FROM public_cursor INTO @DB_Name 

		WHILE @@FETCH_STATUS = 0 
		BEGIN 

		SET @SQL = 'USE ' + QUOTENAME(@DB_Name) + '; ' +
		'SELECT 3, 339, 2
		, ''Public permissions''
		, ''Permissions for the public role''
		, db_name() as [DatabaseName]
		, ''The [public] role has been granted the permission ['' + per.permission_name + ''] on the object [''
		+ CASE
		WHEN per.class = 0 THEN db_name()
		WHEN per.class = 3 THEN schema_name(major_id)
		WHEN per.class = 4 THEN printarget.NAME
		WHEN per.class = 5 THEN asm.NAME
		WHEN per.class = 6 THEN type_name(major_id)
		WHEN per.class = 10 THEN xmlsc.NAME
		WHEN per.class = 15 THEN msgt.NAME COLLATE DATABASE_DEFAULT
		WHEN per.class = 16 THEN svcc.NAME COLLATE DATABASE_DEFAULT
		WHEN per.class = 17 THEN svcs.NAME COLLATE DATABASE_DEFAULT
		WHEN per.class = 18 THEN rsb.NAME COLLATE DATABASE_DEFAULT
		WHEN per.class = 19 THEN rts.NAME COLLATE DATABASE_DEFAULT
		WHEN per.class = 23 THEN ftc.NAME
		WHEN per.class = 24 THEN sym.NAME
		WHEN per.class = 25 THEN crt.NAME
		WHEN per.class = 26 THEN asym.NAME
		END + ''].''
		, ''Because these permissions are available to anyone who can connect to your instance, they should be revoked and granted to users, groups, or roles other than public.''
		, ''https://straightpathsql.com/check/explicit-permissions-for-public''
		FROM sys.database_permissions AS per
		LEFT JOIN sys.database_principals AS prin ON per.grantee_principal_id = prin.principal_id
		LEFT JOIN sys.assemblies AS asm ON per.major_id = asm.assembly_id
		LEFT JOIN sys.xml_schema_collections AS xmlsc ON per.major_id = xmlsc.xml_collection_id
		LEFT JOIN sys.service_message_types AS msgt ON per.major_id = msgt.message_type_id
		LEFT JOIN sys.service_contracts AS svcc ON per.major_id = svcc.service_contract_id
		LEFT JOIN sys.services AS svcs ON per.major_id = svcs.service_id
		LEFT JOIN sys.remote_service_bindings AS rsb ON per.major_id = rsb.remote_service_binding_id
		LEFT JOIN sys.routes AS rts ON per.major_id = rts.route_id
		LEFT JOIN sys.database_principals AS printarget ON per.major_id = printarget.principal_id
		LEFT JOIN sys.symmetric_keys AS sym ON per.major_id = sym.symmetric_key_id
		LEFT JOIN sys.asymmetric_keys AS asym ON per.major_id = asym.asymmetric_key_id
		LEFT JOIN sys.certificates AS crt ON per.major_id = crt.certificate_id
		LEFT JOIN sys.fulltext_catalogs AS ftc ON per.major_id = ftc.fulltext_catalog_id
		WHERE per.grantee_principal_id = DATABASE_PRINCIPAL_ID(''public'')
			AND class != 1 -- Object or Columns (class = 1) are handled by VA1054 and have different remediation syntax
			AND [state] IN (''G'',''W'')
			AND NOT (
				per.class = 0
				AND prin.NAME = ''public''
				AND per.major_id = 0
				AND per.minor_id = 0
				AND permission_name IN (
					''VIEW ANY COLUMN ENCRYPTION KEY DEFINITION''
					,''VIEW ANY COLUMN MASTER KEY DEFINITION''
					)
				)';
		
			INSERT #Results
			EXEC sp_executesql @SQL ;

			/* iterate the cursor to the next database name */
			FETCH NEXT FROM public_cursor INTO @DB_Name;
		END 
		CLOSE public_cursor;
		DEALLOCATE public_cursor;
		END;

	END;

/* results */
IF @Mode = 1
	SELECT
		CASE [Importance]
            WHEN 1 THEN '1 - High'
		    WHEN 2 THEN '2 - Medium'
			ELSE '3 - Low'
		END AS [Importance]
        , CheckName
        , Issue
        , DatabaseName
        , Details
        , ActionStep
        , ReadMoreURL
		, CheckID
    FROM #Results
    WHERE [Importance] = 1
    ORDER BY
        [Importance]
		, CheckName
        , DatabaseName
        , Details;


IF @Mode = 0
	SELECT
		CASE [Importance]
            WHEN 0 THEN '0 - Information'
			WHEN 1 THEN '1 - High'
		    WHEN 2 THEN '2 - Medium'
			ELSE '3 - Low'
		END AS [Importance]
        , CheckName
        , Issue
        , DatabaseName
        , Details
        , ActionStep
        , ReadMoreURL
		, CheckID
    FROM #Results
    WHERE [Importance] > 0
    ORDER BY
        [Importance]
		, CheckName
        , DatabaseName
        , Details;
  
  IF @Mode = 99
	SELECT
		CASE [Importance]
            WHEN 0 THEN '0 - Information'
            WHEN 1 THEN '1 - High'
		    WHEN 2 THEN '2 - Medium'
			ELSE '3 - Low'
		END AS [Importance]
        , CheckName
        , Issue
        , DatabaseName
        , Details
        , ActionStep
        , ReadMoreURL
		, CheckID
    FROM #Results
    ORDER BY
        [Importance]
		, CheckName
        , DatabaseName
        , Details;
GO