EitanBlumin

/*
Invalid owner for a system Role, Schema, or Database
====================================================
Author: Eitan Blumin | Madeira Data Solutions | https://www.madeiradata.com
Date: 2020-11-25
Description:

System roles and schemas must have specific owning users or roles.
 
For example, all system database roles such as db_owner, db_datawriter, db_datareader, etc. must be owned by dbo.

EitanBlumin

/******************************************************************
		Parse SQL Vulnerability Assessment Tool Results
		***********************************************
Author: Eitan Blumin | https://www.eitanblumin.com

Description:
	Use this script to parse a Vulnerability Assessment Tool
	results file into a relational structure and save in an HTML page.
	This will output the T-SQL queries used by VAT behind the scenes
	and their respective meta-data, as displayed in the VAT.

EitanBlumin

/*
Check that the configured value for MAXDOP is in the recommended range, as described in this KB article: 
https://support.microsoft.com/en-us/help/2806535/recommendations-and-guidelines-for-the-max-degree-of-parallelism-confi

If @WhatIf = 0 then MAXDOP will automatically be changed to the recommended setting.
*/
-- change this to 1 to only display findings without actually changing the config:
DECLARE @WhatIf BIT = 0;

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

EitanBlumin

/*
=================================================
Reproduce an access violation error in SQL Server
=================================================
The following script reproduces an Access Violation error
caused by a parallelism plan involving specific system table functions.
The error occurses ONLY with parallel execution plans.

Discovered by Eitan Blumin and Nathan Lifshes on 2020-09-06
The error has been reproduced in multiple SQL Server versions that were tested:

EitanBlumin

SELECT SiteName, ReplicaName
	, NumberOfAvailabilityGroups = COUNT(DISTINCT AGName)
	, NumberOfPrimaries = COUNT(DISTINCT PrimaryReplica)
FROM
(
SELECT DISTINCT
	ES.ObjectName AS ReplicaName, S.Name AS SiteName
	, AG.Name AS AGName
	, AG.PrimaryReplica
FROM [SentryOne].[AlwaysOn].[AvailabilityGroup] AS AG

EitanBlumin

/*
=======================================================
Find Top Exec Plans to Optimize
=======================================================
Author: Eitan Blumin | eitanblumin.com , madeiradata.com
Date: 2020-08-12
Description:
Use this script to discover execution plans with a good
potential for performance optimization.
Finds execution plans with warnings and problematic operators.

EitanBlumin

IF OBJECT_ID('dbo.ArchivingActivityLog') IS NULL
BEGIN
	CREATE TABLE dbo.ArchivingActivityLog
	(
		Id INT NOT NULL IDENTITY(1,1),
		SourceTable SYSNAME NOT NULL,
		Command NVARCHAR(MAX) NULL,
		StartTime DATETIME NOT NULL CONSTRAINT DF_ArchivingActivityLog_StartTime DEFAULT (GETDATE()),
		EndTime DATETIME NULL,
		RowsMoved INT NULL,

EitanBlumin

/*
Detect Non Secured Connections (SSL) to the SQL Server instance
===============================================================
Author: Eitan Blumin (t: @EitanBlumin | b: eitanblumin.com)
Last Update: 2020-07-15
Description: Use this to make sure that all connections to the SQL Server instance are secured with SSL.
*/
SELECT CONCAT('Not secured connection(s) detected of '
, ISNULL(QUOTENAME(COALESCE(ses.original_login_name, ses.nt_user_name, ses.login_name)), 'an unknown login')
, ' from ', ISNULL(QUOTENAME(client_net_address), 'an unknown address')

EitanBlumin

/*
Author: Eitan Blumin (t: @EitanBlumin | b: https://eitanblumin.com)
Description: Use this script to retrieve all unused indexes across all of your databases.
The data returned includes various index usage statistics and a corresponding drop command.
Supports both on-premise instances, as well as Azure SQL Databases.
*/
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
DECLARE @CMD NVARCHAR(MAX);
SET @CMD = N'
 PRINT DB_NAME();

EitanBlumin

/*
Author: Eitan Blumin (t: @EitanBlumin | b: https://eitanblumin.com)
Date Created: 2013-09-01
Last Update: 2020-07-28
Description:
	CTE-based Inline Table Function to generate periods for time series, based on an end date, period type, and number of periods back.

Supported period types:
	MI - Minute
	H - Hour