Recent Gists from EitanBlumin

Brute force database out of SINGLE_USER mode back into MULTI_USER mode

2025-11-03T06:28:48Z

	-- run this in query window 1:

	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
	SET DEADLOCK_PRIORITY HIGH;
	GO
	DECLARE @CMD nvarchar(max) = ''
	SELECT @CMD = CONCAT(@CMD, CHAR(10), 'KILL ', session_id)
	FROM sys.dm_exec_requests
	WHERE database_id = DB_ID('MyDB') -- replace MyDB with your DB name

	PRINT @CMD
	EXEC (@CMD);
	GO
	-- replace MyDB with your DB name:
	USE MyDB
	ALTER DATABASE MyDB SET MULTI_USER;

Query window 2.sql

	-- run this in query window 2:

	DECLARE @CMD nvarchar(max);

	WHILE 1=1
	BEGIN
	SET @CMD = NULL;
	SELECT @CMD = ISNULL(@CMD + CHAR(10), '')
	+ CONCAT(N'KILL ', blocking_session_id)
	FROM sys.dm_exec_requests
	WHERE session_id = 1234 -- replace with the session id of query window 1

	IF @CMD IS NULL BREAK;
	RAISERROR(N'%s',0,1,@CMD) WITH NOWAIT;
	IF @CMD <> 'KILL 0' EXEC(@CMD);
	END

Detect misconfigured jobs with retries and output file

2025-06-30T14:31:41Z

Misconfigured jobs with retries and output file.sql

	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

	SELECT
	Msg = CONCAT(N'Job "', j.name, N'" step ', s.step_id, N' ("', s.step_name,N'") ',
	, CARE WHEN s.retry_attempts > 0 THEN N'has retries enabled but ' ELSE N'' END
	, CASE WHEN (s.flags & 2) = 0 THEN N'Append is disabled (which may cause loss of info) ' ELSE N'Append is enabled ' END
	, CASE WHEN
	s.output_file_name NOT LIKE '%(TIME)%'
	AND s.output_file_name NOT LIKE '%(STRTTM)%'
	AND s.output_file_name NOT LIKE '%(STRTDT)%'
	AND s.output_file_name NOT LIKE '%(DATE)%'
	THEN
	N'with no time-based tokens contained in the output file path'
	+ CASE WHEN (s.flags & 2) > 0 THEN N' (which may cause the file to grow out of control) ' ELSE N' (which are needed when append is enabled) ' END
	ELSE N'' END
	, N' for the output file path: ', s.output_file_name)
	, s.step_id
	, RemediationCommand = N'-- REVIEW BEFORE EXECUTION:
	EXEC msdb.dbo.sp_update_jobstep
	@job_name = N' + QUOTENAME(j.name, '''') + ',
	@step_id = ' + CAST(s.step_id AS NVARCHAR(MAX)) + ',
	@output_file_name = N''' +
	CASE
	WHEN s.output_file_name NOT LIKE '%(TIME)%'
	AND s.output_file_name NOT LIKE '%(STRTTM)%'
	AND s.output_file_name NOT LIKE '%(STRTDT)%'
	AND s.output_file_name NOT LIKE '%(DATE)%'
	THEN
	-- Insert tokens before .txt (case-insensitive)
	CASE
	WHEN LOWER(s.output_file_name) LIKE N'%.txt'
	THEN LEFT(s.output_file_name, LEN(s.output_file_name) - 4) + '_$(ESCAPE_SQUOTE(DATE))_$(ESCAPE_SQUOTE(TIME)).txt'
	ELSE s.output_file_name + '_$(ESCAPE_SQUOTE(DATE))_$(ESCAPE_SQUOTE(TIME))'
	END
	ELSE
	s.output_file_name
	END + ''',
	@flags = ' + CAST(s.flags \| 2 AS NVARCHAR(MAX))
	FROM
	msdb.dbo.sysjobs AS j
	INNER JOIN
	msdb.dbo.sysjobsteps AS s ON j.job_id = s.job_id
	WHERE
	j.[enabled] = 1
	AND s.output_file_name IS NOT NULL
	AND LTRIM(RTRIM(s.output_file_name)) <> ''
	AND (
	(s.retry_attempts > 0 AND (s.flags & 2) = 0) -- Append to output file is disabled
	OR
	(
	s.flags & 2 > 0 -- Append enabled
	AND s.output_file_name NOT LIKE '%(TIME)%'
	AND s.output_file_name NOT LIKE '%(STRTTM)%'
	AND s.output_file_name NOT LIKE '%(STRTDT)%'
	AND s.output_file_name NOT LIKE '%(DATE)%'
	)
	)
	ORDER BY
	j.name, s.step_id;

DatabaseIntegrityCheck - Allocation and Catalog Checks Only

2024-10-27T20:22:37Z

DatabaseIntegrityCheck - Allocation and Catalog Checks Only.sql

	DECLARE @MaxEndTime datetime = DATEADD(HOUR, 2, GETDATE())
	DECLARE @TimeLimitSeconds int;

	SET @TimeLimitSeconds = DATEDIFF(second, GETDATE(), @MaxEndTime)

	EXEC dbo.DatabaseIntegrityCheck
	@Databases = 'ALL_DATABASES',
	@CheckCommands = 'CHECKALLOC,CHECKCATALOG',
	--@PhysicalOnly = 'Y',
	@TimeLimit = @TimeLimitSeconds,
	@LogToTable= 'Y',
	@Execute = 'Y'

DatabaseIntegrityCheck - Incremental Object-Level Checks

2024-10-27T20:20:12Z

DatabaseIntegrityCheck - Incremental Object-Level Checks.sql

	DECLARE @EndTime datetime = DATEADD(hour, 2, GETDATE()) -- Adjust the time limit as needed
	DECLARE @OlaHallengrenDBName sysname = DB_NAME() -- This script must run within the context of the database where Ola's maintenance solution was installed

	DECLARE @DBName sysname, @ObjNameFull nvarchar(4000), @ObjNameLean sysname, @SchName sysname
	DECLARE @CheckTime datetime, @LastCheckDate datetime, @ObjType sysname

	IF OBJECT_ID('tempdb..#Objects') IS NOT NULL DROP TABLE #Objects;
	CREATE TABLE #Objects
	(
	DBName sysname,
	SchemaName sysname,
	TableName sysname,
	ObjType sysname,
	UsedPages int,
	LastCheck datetime,
	FullTableName AS (QUOTENAME(DBName) + N'.' + QUOTENAME(SchemaName) + '.' + QUOTENAME(TableName))
	);

	DECLARE @CMD nvarchar(max), @SpExecuteSql nvarchar(4000);
	SET @CMD = N'SELECT DB_NAME()
	, ss.name
	, so.[name]
	, CASE WHEN so.[type] = ''V'' THEN ''VIEW'' ELSE ''TABLE'' END
	, SUM(sps.used_page_count) AS used_page_count
	, ep.[EndTime]
	FROM sys.objects so
	INNER JOIN sys.dm_db_partition_stats sps ON so.[object_id] = sps.[object_id]
	INNER JOIN sys.indexes si ON so.[object_id] = si.[object_id]
	INNER JOIN sys.schemas ss ON so.[schema_id] = ss.[schema_id]
	OUTER APPLY
	(
	SELECT [DatabaseName]
	,[SchemaName]
	,[ObjectName]
	,[ObjectType]
	,MAX([EndTime]) AS [EndTime]
	FROM ' + @OlaHallengrenDBName + N'.[dbo].[CommandLog]
	WHERE CommandType = ''DBCC_CHECKTABLE''
	AND [DatabaseName] COLLATE DATABASE_DEFAULT = DB_NAME() COLLATE DATABASE_DEFAULT
	AND [SchemaName] COLLATE DATABASE_DEFAULT = ss.[name] COLLATE DATABASE_DEFAULT
	AND [ObjectName] COLLATE DATABASE_DEFAULT = so.[name] COLLATE DATABASE_DEFAULT
	AND [ObjectType] COLLATE DATABASE_DEFAULT = so.[type] COLLATE DATABASE_DEFAULT
	GROUP BY
	[DatabaseName]
	,[SchemaName]
	,[ObjectName]
	,[ObjectType]
	) AS ep
	WHERE so.[type] IN (''U'', ''V'')
	GROUP BY so.[object_id], so.[name], ss.name, so.[type], so.type_desc, ep.[EndTime]'

	DECLARE DBs CURSOR
	LOCAL FAST_FORWARD READ_ONLY
	FOR
	SELECT [name]
	FROM sys.databases
	WHERE HAS_DBACCESS([name]) = 1
	AND state = 0
	AND [name] NOT IN ('tempdb')

	OPEN DBs

	WHILE 1=1
	BEGIN
	FETCH NEXT FROM DBs INTO @DBName;
	IF @@FETCH_STATUS <> 0 BREAK;

	SET @SpExecuteSql = QUOTENAME(@DBName) + N'..sp_executesql'

	INSERT INTO #Objects
	(DBName, SchemaName, TableName, ObjType, UsedPages, LastCheck)
	EXEC @SpExecuteSql @CMD

	END

	CLOSE DBs;
	DEALLOCATE DBs;


	DECLARE obj CURSOR
	LOCAL FAST_FORWARD READ_ONLY
	FOR
	SELECT DBName
	, FullTableName
	, SchemaName
	, TableName
	, ObjType
	, LastCheck
	FROM #Objects
	ORDER BY LastCheck ASC, UsedPages DESC

	OPEN obj;

	WHILE GETDATE() < @EndTime
	BEGIN
	FETCH NEXT FROM obj INTO @DBName, @ObjNameFull, @SchName, @ObjNameLean, @ObjType, @LastCheckDate;
	IF @@FETCH_STATUS <> 0 BREAK;

	EXEC [dbo].[DatabaseIntegrityCheck]
	@Databases = @DBName,
	@CheckCommands = 'CHECKTABLE',
	@Objects = @ObjNameFull,
	@Execute = 'Y',
	@LogToTable = 'Y'

	END

	CLOSE obj
	DEALLOCATE obj

ALTER TABLE SWITCH demo for errors 4907 and 4908

2023-07-17T14:05:00Z

ALTER TABLE SWITCH demo errors 4907 and 4908.sql

	/*
	https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-table-transact-sql?view=sql-server-ver16#switch--partition-source_partition_number_expression--to--schema_name--target_table--partition-target_partition_number_expression-
	*/
	SET NOCOUNT ON;
	GO
	CREATE PARTITION FUNCTION PF1 (int) AS RANGE RIGHT FOR VALUES (0, 100)
	CREATE PARTITION SCHEME PS1 AS PARTITION PF1 ALL TO ([PRIMARY]);
	GO
	CREATE PARTITION FUNCTION PF2 (int) AS RANGE RIGHT FOR VALUES (0, 100, 200)
	CREATE PARTITION SCHEME PS2 AS PARTITION PF2 ALL TO ([PRIMARY]);
	GO
	CREATE PARTITION FUNCTION PF3 (int) AS RANGE RIGHT FOR VALUES (0, 200)
	CREATE PARTITION SCHEME PS3 AS PARTITION PF3 ALL TO ([PRIMARY]);
	GO

	-- PrtTable1: The source table
	CREATE TABLE dbo.PrtTable1
	(
	prtCol int NOT NULL CONSTRAINT PK_PrtTable1 PRIMARY KEY CLUSTERED ON PS1(prtCol),
	otherCol int NOT NULL
	) ON PS1(prtCol)

	CREATE NONCLUSTERED INDEX IX1 ON dbo.PrtTable1 (otherCol) ON PS2(prtCol)
	CREATE NONCLUSTERED INDEX IX1_3 ON dbo.PrtTable1 (otherCol) ON PS3(prtCol)

	GO

	-- PrtTable2: Target table partitioned and indexed identically to source table PrtTable1
	CREATE TABLE dbo.PrtTable2
	(
	prtCol int NOT NULL CONSTRAINT PK_PrtTable2 PRIMARY KEY CLUSTERED ON PS1(prtCol),
	otherCol int NOT NULL
	) ON PS1(prtCol)

	CREATE NONCLUSTERED INDEX IX2 ON dbo.PrtTable2 (otherCol) ON PS2(prtCol)
	CREATE NONCLUSTERED INDEX IX2_3 ON dbo.PrtTable2 (otherCol) ON PS3(prtCol)

	GO

	-- PrtTable3: Target table without any nonclustered indexes, partition range not identical but contains the range from source table
	CREATE TABLE dbo.PrtTable3
	(
	prtCol int NOT NULL CONSTRAINT PK_PrtTable3 PRIMARY KEY CLUSTERED ON PS3(prtCol),
	otherCol int NOT NULL
	) ON PS3(prtCol)

	GO
	-- Insert just one row into source table, goes into partition number 2
	INSERT INTO dbo.PrtTable1 (prtCol, otherCol) VALUES(50, 30)
	GO
	SELECT OBJECT_NAME(object_id), index_id, partition_number, rows
	FROM sys.partitions
	WHERE object_id IN (OBJECT_ID('PrtTable1'),OBJECT_ID('PrtTable2'),OBJECT_ID('PrtTable3'))
	GO
	RAISERROR(N'ALTER TABLE dbo.PrtTable1 SWITCH PARTITION 2 TO dbo.PrtTable2 PARTITION 2',0,1) WITH NOWAIT;
	ALTER TABLE dbo.PrtTable1 SWITCH PARTITION 2 TO dbo.PrtTable2 PARTITION 2 -- raises error 4907
	GO
	RAISERROR(N'ALTER TABLE dbo.PrtTable1 SWITCH PARTITION 2 TO dbo.PrtTable3 PARTITION 2',0,1) WITH NOWAIT;
	ALTER TABLE dbo.PrtTable1 SWITCH PARTITION 2 TO dbo.PrtTable3 PARTITION 2 -- also raises error 4907
	GO
	PRINT N'Moving IX1 to same PS as the table PrtTable1'
	CREATE NONCLUSTERED INDEX IX1 ON dbo.PrtTable1 (otherCol) WITH(DROP_EXISTING = ON) ON PS1(prtCol)
	GO
	RAISERROR(N'ALTER TABLE dbo.PrtTable1 SWITCH PARTITION 2 TO dbo.PrtTable2 PARTITION 2',0,1) WITH NOWAIT;
	ALTER TABLE dbo.PrtTable1 SWITCH PARTITION 2 TO dbo.PrtTable2 PARTITION 2 -- raises error 4908 due to different range boundaries
	GO
	PRINT N'Moving IX1_3 to same PS as the table PrtTable1'
	CREATE NONCLUSTERED INDEX IX1_3 ON dbo.PrtTable1 (otherCol) WITH(DROP_EXISTING = ON) ON PS1(prtCol)
	GO
	RAISERROR(N'ALTER TABLE dbo.PrtTable1 SWITCH PARTITION 2 TO dbo.PrtTable2 PARTITION 2',0,1) WITH NOWAIT;
	ALTER TABLE dbo.PrtTable1 SWITCH PARTITION 2 TO dbo.PrtTable2 PARTITION 2 -- raises error 4907, but this time on the target table
	GO
	RAISERROR(N'ALTER TABLE dbo.PrtTable1 SWITCH PARTITION 2 TO dbo.PrtTable3 PARTITION 2',0,1) WITH NOWAIT;
	ALTER TABLE dbo.PrtTable1 SWITCH PARTITION 2 TO dbo.PrtTable3 PARTITION 2 -- without nonclustered index on target: works (even though range boundaries are different, they're still matching)
	PRINT N'Success'
	GO
	SELECT OBJECT_NAME(object_id), index_id, partition_number, rows
	FROM sys.partitions
	WHERE object_id IN (OBJECT_ID('PrtTable1'),OBJECT_ID('PrtTable2'),OBJECT_ID('PrtTable3'))
	GO
	-- Cleanup:
	DROP TABLE IF EXISTS dbo.PrtTable1
	DROP TABLE IF EXISTS dbo.PrtTable2
	DROP TABLE IF EXISTS dbo.PrtTable3
	DROP PARTITION SCHEME PS1
	DROP PARTITION FUNCTION PF1
	DROP PARTITION SCHEME PS2
	DROP PARTITION FUNCTION PF2
	DROP PARTITION SCHEME PS3
	DROP PARTITION FUNCTION PF3
	GO

Demo creation of tables with system-named constraints

2023-01-13T09:02:57Z

system_named_constraints_demo.sql

	DROP TABLE IF EXISTS [dbo].[TestTable2];
	DROP TABLE IF EXISTS [dbo].[TestTable1];
	DROP TABLE IF EXISTS dbo.TestEdgeTable;
	DROP TABLE IF EXISTS dbo.TestNodeTable1;
	DROP TABLE IF EXISTS dbo.TestNodeTable2;
	DROP TABLE IF EXISTS dbo.TestNodeTable3;
	GO
	IF SCHEMA_ID('EitanTest') IS NOT NULL DROP SCHEMA EitanTest;
	GO
	CREATE SCHEMA EitanTest AUTHORIZATION dbo;
	GO

	CREATE TABLE EitanTest.[TestTable1](
	[ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
	[InsertTime] [datetime] NOT NULL DEFAULT (GETUTCDATE()),
	[Salary] [int] NOT NULL CHECK ([Salary]>=0),
	[Email] nvarchar(128) NOT NULL UNIQUE CHECK ([Email] LIKE N'_%@_%._%'),
	[ParentID] [int] NULL FOREIGN KEY REFERENCES EitanTest.[TestTable1](ID),
	CHECK ([InsertTime] > '2000-01-01' AND [Salary] > 1)
	);

	CREATE TABLE [dbo].[TestTable2](
	[ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
	[HeadID] [int] NOT NULL FOREIGN KEY REFERENCES EitanTest.[TestTable1] ([ID]),
	[Email2] nvarchar(128) NULL FOREIGN KEY REFERENCES EitanTest.[TestTable1] ([Email])
	);

	-- If SQL 2019 or newer
	IF (CONVERT(FLOAT, (@@microsoftversion / 0x1000000) & 0xff)) >= 15
	BEGIN
	EXEC(N'
	-- CREATE node and edge tables
	CREATE TABLE dbo.TestNodeTable1
	(
	ID INTEGER PRIMARY KEY
	, CustomerName VARCHAR(100)
	)
	AS NODE;

	CREATE TABLE dbo.TestNodeTable2
	(
	ID INTEGER PRIMARY KEY
	, SupplierName VARCHAR(100)
	)
	AS NODE;

	CREATE TABLE dbo.TestNodeTable3
	(
	ID INTEGER PRIMARY KEY
	, ProductName VARCHAR(100)
	)
	AS NODE;

	-- CREATE edge table with edge constraints.
	CREATE TABLE dbo.TestEdgeTable
	(
	PurchaseCount INT
	, CONNECTION (dbo.TestNodeTable1 TO dbo.TestNodeTable3, dbo.TestNodeTable2 TO dbo.TestNodeTable3)
	, CONNECTION (dbo.TestNodeTable2 TO dbo.TestNodeTable1)
	)
	AS EDGE;'
	);
	END

SQL YAML Build Configuration for Azure Pipelines

2022-05-08T08:19:29Z

SQLDatabaseYAMLBuildConfiguration.yml

	# SQL Database Project Build Configuration

	trigger:
	- master

	pool:
	vmImage: 'VS2017-Win2016'

	variables:
	solution: '*/.sln'
	buildPlatform: 'Any CPU'
	buildConfiguration: 'Release'

	steps:

	- task: VSBuild@1
	inputs:
	solution: '$(solution)'
	platform: '$(buildPlatform)'
	configuration: '$(buildConfiguration)'


	- task: CopyFiles@2
	displayName: 'Copy Files to: $(build.artifactstagingdirectory)'
	inputs:
	SourceFolder: '$(system.defaultworkingdirectory)'
	Contents: '\bin\$(BuildConfiguration)\'
	TargetFolder: '$(build.artifactstagingdirectory)'


	- task: PublishBuildArtifacts@1
	displayName: 'Publish artifacts'
	inputs:
	PathtoPublish: $(build.artifactstagingdirectory)
	ArtifactName: 'PublishBuildArtifacts'

Example job command to implement self-monitoring for SQL Sentry monitoring service hearbeats

2022-04-01T18:44:57Z

SQLSentry_Heartbeat_Monitoring.sql

	-- Run this in the right SQL Sentry database
	--USE [SentryOne];
	--USE [SQLSentry];
	GO
	IF OBJECT_ID('[dbo].[heartbeat_log]') IS NULL
	BEGIN
	CREATE TABLE [dbo].[heartbeat_log](
	[servername] [nvarchar](300) CONSTRAINT PK_Heartbeat_Log PRIMARY KEY CLUSTERED WITH(IGNORE_DUP_KEY=ON,DATA_COMPRESSION=PAGE),
	[heartbeatdate] [datetime] NULL,
	[ActualHeartbeatDate] [datetime] NULL
	);
	END
	GO
	DECLARE
	@HeartbeatThresholdMinutes INT = 5,
	@HeartbeatRepeatAlertThresholdHours INT = 1,
	@GlobalObjID UNIQUEIDENTIFIER

	SELECT @GlobalObjID = ObjectID FROM dbo.vwObjects_Global;
	DECLARE @SubjectTitle NVARCHAR(255), @BodyText NVARCHAR(4000);
	SET @SubjectTitle = N'Monitoring Service: Heartbeat Failure'

	-- Delete any irrelevant heartbeat failures from log
	DELETE HL
	-- optionally use this output to implement an "auto-resolve" logic:
	-- OUTPUT deleted.servername INTO @RestoredHeartbeats
	FROM dbo.heartbeat_log AS HL
	INNER JOIN dbo.ManagementEngine AS E
	ON HL.servername = E.servername
	WHERE E.HeartbeatDateTime > HL.HeartbeatDate

	-- Get all monitoring servers with outdated heartbeats and are not snoozed
	DECLARE @site NVARCHAR(200), @servername NVARCHAR(200), @HeartbeatDateTime datetime
	, @text NVARCHAR(200), @title NVARCHAR(200), @HeartbeatDateTimeLocal datetime;

	DECLARE serverlist CURSOR
	LOCAL FAST_FORWARD
	FOR
	SELECT T.[Name],E.[servername], ISNULL(E.HeartbeatDateTime, E.LastInitializationDateTime)
	FROM dbo.ManagementEngine E
	INNER JOIN [dbo].[Site] T ON T.ID = E.SiteID
	WHERE ISNULL(E.HeartbeatDateTime, E.LastInitializationDateTime) < DATEADD(MINUTE, -@HeartbeatThresholdMinutes, GETUTCDATE())
	AND NOT EXISTS (SELECT st.ObjectID FROM dbo.SnoozeStatus st WHERE st.ObjectID IN (E.ObjectID,T.ObjectID,@GlobalObjID) AND st.ConditionID IS NULL )

	OPEN serverlist

	WHILE 1=1
	BEGIN
	FETCH NEXT FROM serverlist INTO @site, @servername, @HeartbeatDateTime
	IF @@FETCH_STATUS <> 0 BREAK;

	SET @HeartbeatDateTimeLocal = CONVERT(datetime, @HeartbeatDateTime AT TIME ZONE 'UTC' AT TIME ZONE 'Israel Standard Time' )
	SET @title = @site + ': '+ QUOTENAME(@servername) + ' ' + @SubjectTitle
	SET @text = N'Monitor server <b>' + @servername + N'</b> has lost connection to the repository.<br/>Last heartbeat was at <b>' + convert(nvarchar(200),@HeartbeatDateTimeLocal, 121) + N' (IST)</b>'

	SET @BodyText = N'[Message]: ' + @text
	+ N'<br/><br/>[Start Time (Local)]: ' + CONVERT(nvarchar(25), @HeartbeatDateTimeLocal, 121)
	+ N'<br/>[Start Time (UTC)]: ' + CONVERT(nvarchar(25), @HeartbeatDateTime, 121)
	+ N'<br/>[Duration]: ' + ISNULL(NULLIF(CONVERT(varchar(100), DATEDIFF(dd,0, GETUTCDATE()-@HeartbeatDateTime)), 0) + ' day(s), ', '')
	+ CONVERT(varchar(100), GETUTCDATE()-@HeartbeatDateTime, 114)
	+ N'<br/>----------------------------------------------------------------------'
	+ N'<br/>[Timestamp (Local)]: ' + CONVERT(nvarchar(25), GETDATE(), 121)
	+ N'<br/>[Timestamp (UTC)]: ' + CONVERT(nvarchar(25), GETUTCDATE(), 121)
	+ N'<br/>[Generated By]: ' + @@SERVERNAME
	+ N'<br/>[Monitor Type]: SQLSentryHeartbeatCheck<br/>[Condition]: ' + @SubjectTitle
	;
	WITH trgt AS (SELECT * FROM dbo.heartbeat_log WHERE servername = @servername)
	MERGE INTO trgt
	USING (SELECT * FROM (VALUES(@servername, GETUTCDATE())) AS v(servername, TimeNow)) AS src
	ON trgt.servername = src.servername
	WHEN NOT MATCHED BY TARGET THEN
	INSERT (servername, heartbeatdate)
	VALUES (servername, TimeNow)
	WHEN MATCHED AND src.TimeNow > dateadd(hour, @HeartbeatRepeatAlertThresholdHours, trgt.heartbeatdate) THEN
	UPDATE SET heartbeatdate = src.TimeNow
	;

	IF @@ROWCOUNT > 0
	BEGIN
	PRINT N'Heartbeat failure for ' + QUOTENAME(@servername)

	-- Get additional metadata about the server if it's also a SQL Server
	DECLARE @SQLVersion NVARCHAR(4000), @SQLEdition NVARCHAR(4000)
	SELECT
	@SQLVersion = CASE
	WHEN MajorVersionNumber = 10 AND MinorVersionNumber = 50 THEN '2008R2'
	WHEN EngineEdition IN (5,6) THEN 'sql_azure'
	ELSE
	CASE MajorVersionNumber
	WHEN 8 THEN '2000'
	WHEN 9 THEN '2005'
	WHEN 10 THEN '2008'
	WHEN 11 THEN '2012'
	WHEN 12 THEN '2014'
	WHEN 13 THEN '2016'
	WHEN 14 THEN '2017'
	WHEN 15 THEN '2019'
	WHEN 16 THEN '2022'
	ELSE ''
	END
	END
	,@SQLEdition = CASE [EngineEdition] -- https://docs.microsoft.com/en-us/sql/t-sql/functions/serverproperty-transact-sql
	WHEN 2 THEN 'standard' -- also web and BI
	WHEN 3 THEN 'enterprise' -- also datacenter, developer and evaluation
	WHEN 4 THEN 'express'
	WHEN 5 THEN 'azure_sql_database'
	WHEN 6 THEN 'azure_sql_data_warehouse'
	WHEN 8 THEN 'azure_managed_instance'
	ELSE ''
	END
	FROM [dbo].[vwSqlServer]
	WHERE [ObjectName] = @servername

	/* ---- TODO: Add your alert logic below this line ---- */

	/*-- Example 1: Sending an e-mail using DBMail:
	EXEC msdb..sp_send_dbmail
	@profile_name = 'dbmail_profile'
	, @recipients = '[email protected]'
	, @subject = @title
	, @body = @BodyText
	, @body_format = 'HTML'
	, @importance = 'high'
	*/


	/*-- Example 2: Throwing an error to fail the job:
	--RAISERROR(N'%s', 16, 1, @BodyText);
	*/

	/*-- Example 3: Whatever else:
	EXECUTE SendSNMPAlertCLR
	@SourceServer = @servername,
	@Subject = @title,
	@Body = @BodyText,
	@Severity = 3,
	@SQLVersion = @SQLVersion,
	@SQLEdition = @SQLEdition,
	@Condition = @SubjectTitle,
	@Site = @site
	*/

	/* ---- TODO: Add your alert logic above this line ---- */
	END
	END

	CLOSE serverlist
	DEALLOCATE serverlist

Check for Low PAGE Compression Success Rates

2022-01-16T08:31:26Z

Low PAGE Compression Success Rates.sql

	/*
	Check for low PAGE compression success rates
	============================================
	Author: Eitan Blumin
	Date: 2022-01-13
	Based on blog post by Paul Randal:
	https://www.sqlskills.com/blogs/paul/the-curious-case-of-tracking-page-compression-success-rates/
	*/
	DECLARE
	/* threshold parameters: */
	@MinimumCompressionAttempts int = 200
	,@MaxAttemptSuccessRatePercentage int = 20

	/* change index rebuild options as needed: */
	,@RebuildOptions nvarchar(MAX) = N'ONLINE = ON, SORT_IN_TEMPDB = ON, MAXDOP = 4'

	SET NOCOUNT ON;
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

	DECLARE @Results AS TABLE
	(
	[database_name] sysname NOT NULL, [schema_name] sysname NULL, [table_name] sysname NULL, [index_name] sysname NULL,
	partition_number int NULL, total_rows int NULL, is_partitioned bit NULL, attempts_count int NOT NULL, success_count int NOT NULL,
	range_scans_percent int NULL, updates_percent int NULL,
	success_rate AS (success_count * 1.0 / NULLIF(attempts_count,0))
	);

	DECLARE @CurrDB sysname, @SpExecuteSql nvarchar(1000);
	DECLARE DBs CURSOR
	LOCAL FAST_FORWARD
	FOR
	SELECT [name]
	FROM sys.databases
	WHERE state = 0
	AND source_database_id IS NULL
	AND database_id > 2
	AND HAS_DBACCESS([name]) = 1
	AND DATABASEPROPERTYEX([name],'Updateability') = 'READ_WRITE'

	OPEN DBs;
	WHILE 1=1
	BEGIN
	FETCH NEXT FROM DBs INTO @CurrDB;
	IF @@FETCH_STATUS <> 0 BREAK;

	SET @SpExecuteSql = QUOTENAME(@CurrDB) + N'..sp_executesql'

	INSERT INTO @Results
	EXEC @SpExecuteSql N'
	SELECT DISTINCT
	db_name(),
	object_schema_name (i.object_id),
	object_name (i.object_id),
	i.name,
	p.partition_number,
	p.[rows],
	is_partitioned =
	CASE WHEN EXISTS
	(SELECT NULL FROM sys.partitions AS p2
	WHERE p2.partition_number > 1
	AND p2.object_id = p.object_id
	AND p2.index_id = p.index_id)
	THEN 1 ELSE 0 END,
	page_compression_attempt_count,
	page_compression_success_count,
	range_scans_percent = ISNULL(
	FLOOR(ISNULL(ios.range_scan_count,0) * 1.0 /
	NULLIF(
	ISNULL(ios.range_scan_count,0) +
	ISNULL(ios.leaf_delete_count,0) +
	ISNULL(ios.leaf_insert_count,0) +
	ISNULL(ios.leaf_page_merge_count,0) +
	ISNULL(ios.leaf_update_count,0) +
	ISNULL(ios.singleton_lookup_count,0)
	, 0) * 100.0), 0),
	updates_percent = ISNULL(
	CEILING(ISNULL(ios.leaf_update_count, 0) * 1.0 /
	NULLIF(
	ISNULL(ios.range_scan_count,0) +
	ISNULL(ios.leaf_delete_count,0) +
	ISNULL(ios.leaf_insert_count,0) +
	ISNULL(ios.leaf_page_merge_count,0) +
	ISNULL(ios.leaf_update_count,0) +
	ISNULL(ios.singleton_lookup_count,0)
	, 0) * 100.0), 0)
	FROM sys.indexes AS i
	INNER JOIN sys.partitions AS p ON p.object_id = i.object_id AND p.index_id = i.index_id
	CROSS APPLY sys.dm_db_index_operational_stats (db_id(), p.object_id, p.index_id, p.partition_number) AS ios
	WHERE
	p.data_compression = 2
	AND page_compression_attempt_count >= @MinimumCompressionAttempts
	AND page_compression_success_count * 1.0 / NULLIF(page_compression_attempt_count,0) <= @MaxAttemptSuccessRatePercentage / 100.0
	', N'@MinimumCompressionAttempts int, @MaxAttemptSuccessRatePercentage int'
	, @MinimumCompressionAttempts, @MaxAttemptSuccessRatePercentage

	END

	CLOSE DBs;
	DEALLOCATE DBs;

	SELECT *
	, RemediationCmd = N'USE ' + QUOTENAME([database_name]) + N'; ALTER '
	+ CASE WHEN [index_name] IS NULL THEN N'TABLE '
	ELSE N'INDEX ' + QUOTENAME([index_name]) + N' ON '
	END
	+ QUOTENAME([schema_name]) + N'.' + QUOTENAME([table_name])
	+ CASE WHEN is_partitioned = 1 THEN N' PARTITION ' + CONVERT(nvarchar(MAX), partition_number)
	ELSE N''
	END
	+ N' REBUILD WITH(DATA_COMPRESSION = ROW'
	+ ISNULL(N', ' + NULLIF(@RebuildOptions, N''), N'')
	+ N');'
	FROM @Results
	ORDER BY success_rate ASC

Hypothetical Indexes - Example Usage script

2021-12-13T14:42:57Z

Hypothetical Indexes - Example Usage.sql


	SET SHOWPLAN_XML ON;
	GO

	/* TODO: Add your test query here to get its estimated plan WITHOUT the hypothetical indexes */

	GO
	SET SHOWPLAN_XML OFF;
	GO

	-- step 1: CREATE
	-- CREATE hypothetical indexes using the WITH STATISTICS_ONLY clause:

	/* TODO: Replace with your own index definitions, but don't forget to use WITH STATISTICS_ONLY */

	CREATE NONCLUSTERED INDEX [IX_Hypothetical]
	ON [dbo].[MyTableName] ( [column1], [column2] )
	WITH STATISTICS_ONLY

	GO

	-- step 2: AUTOPILOT
	-- Generate and run DBCC AUTOPILOT commands to mark ALL hypothetical indexes for AUTOPILOT:

	DECLARE @TableName nvarchar(256) = NULL -- '[dbo].[MyTableName]' -- optionally filter by table

	-- DBCC AUTOPILOT (typeid [, dbid [, {maxQueryCost \| tabid [, indid [, pages [, flag [, rowcounts]]]]} ]])
	DECLARE @cmd nvarchar(max)

	DECLARE cmd CURSOR
	LOCAL FAST_FORWARD
	FOR
	SELECT N'DBCC AUTOPILOT(0,' + CONVERT(nvarchar(MAX), DB_ID()) + N',' + CONVERT(nvarchar(MAX), object_id) + N',' + CONVERT(nvarchar(MAX), index_id) + N');'
	FROM sys.indexes
	WHERE is_hypothetical = 1
	AND (@TableName IS NULL OR object_id = OBJECT_ID(@TableName))

	OPEN cmd

	WHILE 1=1
	BEGIN
	FETCH NEXT FROM cmd INTO @cmd
	IF @@FETCH_STATUS <> 0 BREAK;

	PRINT @cmd;
	EXEC(@cmd);
	END

	CLOSE cmd
	DEALLOCATE cmd
	GO

	-- step 3: GENERATE ESTIMATED PLAN
	-- run the below to generate an estimated plan assuming the existence of hypothetical indexes marked for autopilot
	-- WARNING: This was found to sometimes cause SQL Crash Dumps, specifically when canceled mid-execution.
	GO
	SET AUTOPILOT ON;
	GO

	/* TODO: Add your test query here to get its estimated plan WITH the hypothetical indexes */

	GO
	SET AUTOPILOT OFF;
	GO

	-- step 4: CLEANUP
	-- Generate and run DROP commands for ALL hypothetical indexes

	DECLARE @TableName nvarchar(256) = NULL -- '[dbo].[MyTableName]' -- optionally filter by table

	DECLARE @cmd nvarchar(max)

	DECLARE cmd CURSOR
	LOCAL FAST_FORWARD
	FOR
	SELECT N'DROP INDEX ' + QUOTENAME(name) + N' ON ' + QUOTENAME(OBJECT_SCHEMA_NAME(object_id)) + N'.' + QUOTENAME(OBJECT_NAME(object_id)) + N';'
	FROM sys.indexes
	WHERE is_hypothetical = 1
	AND (@TableName IS NULL OR object_id = OBJECT_ID(@TableName))

	OPEN cmd

	WHILE 1=1
	BEGIN
	FETCH NEXT FROM cmd INTO @cmd
	IF @@FETCH_STATUS <> 0 BREAK;

	PRINT @cmd;
	EXEC(@cmd);
	END

	CLOSE cmd
	DEALLOCATE cmd
	GO

Moves files to a new location for a SQL Server database in an AlwaysOn Availability Group

2021-10-13T18:23:02Z

Move-SqlFilesAlwaysOn.ps1

	# Copyright 2021 Eitan Blumin <@EitanBlumin, https://www.eitanblumin.com>
	# while at Madeira Data Solutions <https://www.madeiradata.com>
	#
	# Licensed under the MIT License (the "License");
	#
	# 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.

	<#
	.SYNOPSIS
	Moves files to a new location for a SQL Server database in an AlwaysOn Availability Group.

	Author: Eitan Blumin (@EitanBlumin) \| Madeira Data Solutions (@Madeira_Data)
	License: MIT License

	.DESCRIPTION
	Moves files to a new location for a SQL Server database in an AlwaysOn Availability Group.
	The script performs the following operations:
	- Makes sure the destination folder(s) exist.
	- Suspends and removes the specified database from its Availability Group.
	- Executes ALTER DATABASE .. MODIFY FILE .. to change the database file paths.
	- If connected to the PRIMARY replica: Takes the database offline.
	- If connected to the SECONDARY replica: Takes the whole MSSQLSERVER service down.
	- Actually moves the files to their new destination, while retaining the file permissions and ownership (so that the SQL Server won't get an "Access Denied" error).
	- Brings the database / service back online.
	- Adds the database back to the Availability Group.

	.EXAMPLE
	C:\PS> .\Move-SqlFilesAlwaysOn.ps1 -DatabaseName "TestDB" -NewDataFolderPath "D:\MSSQL\Data\" -NewLogFolderPath "L:\MSSQL\Log\"

	.NOTES
	This is an open-source project developed by Eitan Blumin while an employee at Madeira Data Solutions, Madeira Ltd.

	A few remarks:
	- Note the parameters for the script. Be sure you're providing the proper values as input.
	- The script was not tested with database files that aren't mdf/ndf/ldf (such as FILESTREAM, Full-Text Catalogs, and In-Memory). So, I cannot guarantee that those will work. But if you're feeling adventurous, you can use the switch parameter "AllowNonDataOrLogFileTypes" to try it out anyway.
	- If you're moving large files, remember that it would take a while to finish, during which time the database will not be available.
	- Don't forget to disable backup jobs! The script doesn't do that for you.
	- The script is NOT idempotent. If it fails in the middle of execution, you may have trouble running it again.
	- As always with such things: Test, test, test! I recommend adding a new, small database to your Availability Group and test the script on this database, before trying it out on your larger production databases.

	.LINK
	https://github.com/MadeiraData/MadeiraToolbox/blob/master/Utility%20Scripts/Move-SqlFilesAlwaysOn.ps1

	.LINK
	https://madeiradata.com

	.LINK
	https://eitanblumin.com
	#>
	param
	(
	[Parameter(Mandatory=$true, Position=0)][string]$DatabaseName = "TestDB",
	[Parameter(Mandatory=$true, Position=1)][string]$NewDataFolderPath = "D:\MSSQL\Data\",
	[Parameter(Mandatory=$false, Position=2)][string]$NewLogFolderPath = $NewDataFolderPath,
	[string]$SqlInstanceConnectionString = "Data Source=.;Integrated Security=True;Application Name=Move Availability Group Files",
	[string]$SqlServiceName = "MSSQLSERVER",
	[switch]$SecondaryOnly,
	[switch]$AllowNonDataOrLogFileTypes,
	[switch]$SkipRunningJobsCheck,
	[int32]$MaxRecoveryQueueMB = 1024,
	[string]$logFileFolderPath = "C:\Madeira\log",
	[string]$logFilePrefix = "move_ag_dbfiles_",
	[string]$logFileDateFormat = "yyyyMMdd_HHmmss",
	[int]$logFileRetentionDays = 7
	)
	Process {
	#region initialization
	function Get-TimeStamp {
	Param(
	[switch]$NoWrap,
	[switch]$Utc
	)
	$dt = Get-Date
	if ($Utc -eq $true) {
	$dt = $dt.ToUniversalTime()
	}
	$str = "{0:MM/dd/yy} {0:HH:mm:ss}" -f $dt

	if ($NoWrap -ne $true) {
	$str = "[$str]"
	}
	return $str
	}

	if ($logFileFolderPath -ne "")
	{
	if (!(Test-Path -PathType Container -Path $logFileFolderPath)) {
	Write-Output "$(Get-TimeStamp) Creating directory $logFileFolderPath" \| Out-Null
	New-Item -ItemType Directory -Force -Path $logFileFolderPath \| Out-Null
	} else {
	$DatetoDelete = $(Get-Date).AddDays(-$logFileRetentionDays)
	Get-ChildItem $logFileFolderPath \| Where-Object { $_.Name -like "$logFilePrefix" -and $_.LastWriteTime -lt $DatetoDelete } \| Remove-Item \| Out-Null
	}

	$logFilePath = $logFileFolderPath + "\$logFilePrefix" + (Get-Date -Format $logFileDateFormat) + ".LOG"

	# attempt to start the transcript log, but don't fail the script if unsuccessful:
	try
	{
	Start-Transcript -Path $logFilePath -Append
	}
	catch [Exception]
	{
	Write-Warning "$(Get-TimeStamp) Unable to start Transcript: $($_.Exception.Message)"
	$logFileFolderPath = ""
	}
	}
	#endregion initialization

	$ErrorActionPreference = "Stop"

	#region main

	# Get metadata for the given database, and do some basic validation
	$AGName = $null
	$IsSecondary = $false

	$result = Invoke-Sqlcmd -ConnectionString $SqlInstanceConnectionString -Query "SELECT ag.name,
	replica_states.role_desc,
	CONVERT(sysname, SERVERPROPERTY('ServerName')) AS ServerName
	FROM sys.databases db
	INNER JOIN sys.dm_hadr_availability_replica_states replica_states ON db.replica_id = replica_states.replica_id
	INNER JOIN sys.availability_groups ag ON replica_states.group_id = ag.group_id
	WHERE db.name = '$DatabaseName'" -Verbose -AbortOnError -OutputSqlErrors $true -ErrorAction Stop

	if ($result)
	{
	$AGName = $result['name']

	if ($result['ServerName'] -ne $env:COMPUTERNAME)
	{
	Write-Error "This script must be run locally from within one of the AG replica servers." -ErrorAction Stop
	}

	if ($result['role_desc'] -eq 'SECONDARY') {
	$IsSecondary = $true
	} elseif ($SecondaryOnly) {
	Write-Error "This script must be run locally from within the SECONDARY replica only." -ErrorAction Stop
	}
	} else {
	Write-Error "Database [$DatabaseName] is not part of an availability group." -ErrorAction Stop
	}

	# Make sure this script is run as an Administrator
	If (-NOT ([Security.Principal.WindowsPrincipal] [Security.Principal.WindowsIdentity]::GetCurrent()).IsInRole( [Security.Principal.WindowsBuiltInRole] “Administrator”) )
	{
	Write-Error “You do not have Administrator rights to run this script!`nPlease re-run this script as an Administrator!” -ErrorAction Stop
	}

	if ($IsSecondary) {
	# Get the MSSQLSERVER service object
	try {
	$SqlService = Get-Service -Name $SqlServiceName

	if ($SqlService.Status -eq "Running" -and !$SqlService.CanStop) {
	Write-Error "No access to shut down '$SqlServiceName' service of computer '$env:COMPUTERNAME'" -ErrorAction Stop
	}
	} catch {
	Write-Error "Error accessing '$SqlServiceName' service of computer '$env:COMPUTERNAME'" -ErrorAction Stop
	}

	# Get all services dependent on the MSSQLSERVER service
	$SqlDependentServices = Get-Service -Name $SqlServiceName -DependentServices -ErrorAction Stop

	# Check for high recovery queues
	$recoveryQueues = Invoke-Sqlcmd -ConnectionString $SqlInstanceConnectionString -Query "select counter_name, instance_name, cntr_value
	from sys.dm_os_performance_counters
	where object_name like '%Database Replica%'
	and counter_name like 'Recovery Queue%'
	and cntr_value > $MaxRecoveryQueueMB * 1024
	order by cntr_value desc" -Verbose -AbortOnError -ErrorAction Stop

	if ($recoveryQueues) {
	$recoveryQueues \| Out-Host
	Write-Error "Stopping execution because Availability Group Recovery Queue is too high. Stopping the SQL service at this state may put the Availability Group at risk." -ErrorAction Stop
	}
	}

	while (!$SkipRunningJobsCheck)
	{
	$runningJobs = Invoke-Sqlcmd -ConnectionString $SqlInstanceConnectionString -Query "SELECT j.name, ja.start_execution_date, ja.stop_execution_date, ja.next_scheduled_run_date
	FROM msdb..sysjobactivity as ja
	inner join msdb..sysjobs as j on ja.job_id = j.job_id
	WHERE session_id = (SELECT MAX(session_id) FROM msdb..sysjobactivity)
	AND start_execution_date IS NOT NULL
	AND (stop_execution_date is null or next_scheduled_Run_date between GETDATE() and DATEADD(minute, 5, GETDATE()))" -Verbose -AbortOnError -ErrorAction Stop

	if ($runningJobs)
	{
	$runningJobs \| Out-Host
	$inputResponse = Read-Host "There are jobs currently running or soon to be executed. Are you sure you want to proceed anyway? (C=Continue, R=Retry Check, S=Stop) "
	if ($inputResponse.ToLower().StartsWith("c")) {
	$SkipRunningJobsCheck = $true;
	}
	elseif ($inputResponse.ToLower().StartsWith("s")) {
	Write-Error "Stopping execution because found currently running or soon to be run jobs." -ErrorAction Stop
	}
	}
	}

	# Use T-SQL to make sure target folder(s) exist with proper permissions
	@($NewDataFolderPath,$NewLogFolderPath) \| ForEach {
	Invoke-Sqlcmd -ConnectionString $SqlInstanceConnectionString -Query "set nocount on;
	declare @path varchar(8000) = '$_';

	create table #tmp (
	[FILE_EXISTS] int not null,
	[FILE_IS_DIRECTORY] int not null,
	[PARENT_DIRECTORY_EXISTS] int not null)

	insert into #tmp
	exec xp_fileexist @path;

	if exists ( select * from #tmp where FILE_EXISTS = 1 )
	begin
	raiserror(N'Existing path is not a directory: %s',16,1,@path);
	end
	else if exists (select * from #tmp where FILE_EXISTS = 0 AND FILE_IS_DIRECTORY = 0)
	begin
	raiserror(N'Creating new directory: %s', 0, 1, @path);
	exec xp_create_subdir @path
	end" -Verbose -AbortOnError -OutputSqlErrors $true -ErrorAction Stop
	}

	# Make sure the new folder paths are accessible
	if (!$NewDataFolderPath -or !(Test-Path $NewDataFolderPath -PathType Container)) {
	Write-Error "Unable to access new data folder path: $NewDataFolderPath" -ErrorAction Stop
	}
	if (!$NewLogFolderPath -or !(Test-Path $NewLogFolderPath -PathType Container)) {
	Write-Error "Unable to access new log folder path: $NewLogFolderPath" -ErrorAction Stop
	}

	# Get metadata for all database files
	$dbFiles = Invoke-Sqlcmd -ConnectionString $SqlInstanceConnectionString -Query "SELECT name, type_desc, physical_name
	FROM sys.master_files
	WHERE database_id = DB_ID('$DatabaseName');" -Verbose -AbortOnError -OutputSqlErrors $true -ErrorAction Stop

	# Validate the path for each file and make sure it's either LOG or ROWS
	# (I cannot guarantee at this point that this script will work with other file types such as FILESTREAM, Full-Text, In-Memory, etc.)
	$dbFiles \| ForEach {
	if ((Test-Path $_.physical_name -PathType Leaf -IsValid -ErrorAction Continue) -and ($_.type_desc -eq "ROWS" -or $_.type_desc -eq "LOG" -or $AllowNonDataOrLogFileTypes))
	{
	Write-Host "OK: $($_.name), type: $($_.type_desc), old path: $($_.physical_name)" -ForegroundColor Green
	} elseif ($_.type_desc -ne "ROWS" -and $_.type_desc -ne "LOG") {
	Write-Error "UNSUPPORTED TYPE: $($_.name), type: $($_.type_desc), old path: $($_.physical_name)" -ErrorAction Stop
	} else {
	Write-Error "INACCESSIBLE: $($_.name), type: $($_.type_desc), old path: $($_.physical_name)" -ErrorAction Stop
	}
	}

	# Suspend and remove the database from the Availability Group
	Write-Output "$(Get-TimeStamp) Removing [$DatabaseName] from Availability Group [$AGName]"

	$cmd = "USE [master];
	ALTER DATABASE [$DatabaseName] SET HADR SUSPEND;"

	if ($IsSecondary) {
	$cmd = $cmd + "
	ALTER DATABASE [$DatabaseName] SET HADR OFF;"
	} else {
	$cmd = $cmd + "
	ALTER AVAILABILITY GROUP [$AGName] REMOVE DATABASE [$DatabaseName];"
	}

	Invoke-Sqlcmd -ConnectionString $SqlInstanceConnectionString -Query $cmd -Verbose -AbortOnError -OutputSqlErrors $true -ErrorAction Stop \| Out-Null

	# Update the database file paths with their new locations
	$dbFiles \| ForEach {

	$currFileName = Split-Path $_.physical_name -Leaf

	if ($_.type_desc -eq "ROWS") {
	$currNewPath = Join-Path -Path $NewDataFolderPath -ChildPath $currFileName
	} else {
	$currNewPath = Join-Path -Path $NewLogFolderPath -ChildPath $currFileName
	}

	Write-Output "$(Get-TimeStamp) Altering file [$($_.name)] with new path: $currNewPath"

	Invoke-Sqlcmd -ConnectionString $SqlInstanceConnectionString -Query "USE [master];
	ALTER DATABASE [$DatabaseName] MODIFY FILE (NAME = [$($_.name)], FILENAME = '$currNewPath');" -Verbose -AbortOnError -OutputSqlErrors $true -ErrorAction Stop \| Out-Null
	}

	if (!$IsSecondary) {

	# If connected to the PRIMARY replica, take the database offline
	Write-Output "$(Get-TimeStamp) Taking [$DatabaseName] offline"

	Invoke-Sqlcmd -ConnectionString $SqlInstanceConnectionString -Query "USE [master];
	ALTER DATABASE [$DatabaseName] SET OFFLINE WITH ROLLBACK IMMEDIATE;" -Verbose -AbortOnError -OutputSqlErrors $true -ErrorAction Stop \| Out-Null

	} elseif ($SqlService.Status -eq "Running") {

	# If connected to the SECONDARY replica, take the MSSQLSERVER service offline
	Write-Output "$(Get-TimeStamp) Stopping service '$SqlServiceName' in computer '$env:COMPUTERNAME'..."

	$SqlService \| Stop-Service -Force -Verbose

	} else {
	Write-Warning "$(Get-TimeStamp) Service status of '$SqlServiceName' in computer '$env:COMPUTERNAME' is: $($SqlService.Status)"
	}

	# Begin moving the files to their new locations
	$dbFiles \| ForEach {

	$currFileName = Split-Path $_.physical_name -Leaf

	# Save the current file's permissions and ownership details
	$currAcl = Get-Acl -Path $_.physical_name

	if ($_.type_desc -eq "LOG") {
	$currNewPath = Join-Path -Path $NewLogFolderPath -ChildPath $currFileName
	} else {
	$currNewPath = Join-Path -Path $NewDataFolderPath -ChildPath $currFileName
	}

	Write-Output "$(Get-TimeStamp) Moving file [$($_.name)] from: $($_.physical_name) to: $currNewPath"

	Move-Item -Path $_.physical_name -Destination $currNewPath -Verbose -ErrorAction Stop \| Out-Null

	# Apply the file-level permissions and ownership in their new location
	$currAcl \| Set-Acl -Path $currNewPath -Verbose
	}


	if (!$IsSecondary) {

	# If connected to the PRIMARY replica, bring the database back online
	Write-Output "$(Get-TimeStamp) Bringing [$DatabaseName] online"

	Invoke-Sqlcmd -ConnectionString $SqlInstanceConnectionString -Query "USE [master];
	ALTER DATABASE [$DatabaseName] SET ONLINE;" -Verbose -AbortOnError -OutputSqlErrors $true -ErrorAction Stop \| Out-Null

	} elseif ($SqlService.Status -eq "Stopped") {

	# If connected to the SECONDARY replica, bring the MSSQLSERVER service and its dependent services back online
	Write-Output "$(Get-TimeStamp) Starting service '$SqlServiceName' in computer '$env:COMPUTERNAME'..."

	$SqlService \| Start-Service -Verbose
	$SqlDependentServices \| Start-Service -Verbose

	} else {
	Write-Warning "$(Get-TimeStamp) Service status of '$SqlServiceName' in computer '$env:COMPUTERNAME' is: $($SqlService.Status)"
	}

	# Add the database back to the Availability Group
	Write-Output "$(Get-TimeStamp) Joining database [$DatabaseName] back to Availability Group [$AGName]"

	if ($IsSecondary) {
	$cmd = "ALTER DATABASE [$DatabaseName] SET HADR AVAILABILITY GROUP = [$AGName];"
	} else {
	$cmd = "ALTER AVAILABILITY GROUP [$AGName] ADD DATABASE [$DatabaseName];"
	}

	Invoke-Sqlcmd -ConnectionString $SqlInstanceConnectionString -Query $cmd -Verbose -AbortOnError -OutputSqlErrors $true -ErrorAction Stop

	# Output new file details
	Invoke-Sqlcmd -ConnectionString $SqlInstanceConnectionString -Query "SELECT name, type_desc, physical_name, state_desc
	FROM sys.master_files
	WHERE database_id = DB_ID('$DatabaseName');" -Verbose -AbortOnError -OutputSqlErrors $true -ErrorAction Stop

	Write-Output "$(Get-TimeStamp) Done"

	#endregion main


	#region finalization
	if ($logFileFolderPath -ne "") { Stop-Transcript }
	#endregion finalization
	}

Checks for tables and indexes with a high percentage of allocated but unused space in all DBs, and generates remediation commands for it

2021-09-06T07:09:19Z

Table and Index High Unused Space.sql

	DECLARE
	@TopPerDB int = 50,
	@MinimumRowCount int = 1000,
	@MinimumUnusedSizeMB int = 1024,
	@MinimumUnusedSpacePct int = 40,
	@RebuildIndexOptions varchar(max) = 'ONLINE = ON, SORT_IN_TEMPDB = ON, MAXDOP = 1' -- , RESUMABLE = ON -- adjust as needed

	SET NOCOUNT, ARITHABORT, XACT_ABORT ON;
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
	DECLARE @command NVARCHAR(MAX);
	DECLARE @TempResult AS TABLE (DatabaseName sysname NOT NULL, SchemaName sysname NULL, TableName sysname NULL, IndexName sysname NULL, Fill_Factor int NULL
	, DatabaseID int NOT NULL, ObjectId int NOT NULL, IndexId int NULL
	, CompressionType tinyint NULL, CompressionType_Desc AS (CASE CompressionType WHEN 0 THEN 'NONE' WHEN 1 THEN 'ROW' WHEN 2 THEN 'PAGE' WHEN 3 THEN 'COLUMNSTORE' WHEN 4 THEN 'COLUMNSTORE_ARCHIVE' ELSE 'UNKNOWN' END)
	, RowCounts bigint NULL, TotalSpaceMB money NULL, UsedSpaceMB money NULL, UnusedSpaceMB money NULL
	, UserSeeks int NULL, UserScans int NULL, UserLookups int NULL, UserUpdates int NULL);

	SELECT @command = 'IF EXISTS (SELECT * FROM sys.databases WHERE [name] = ''?'' AND state = 0 AND HAS_DBACCESS([name]) = 1 AND database_id > 4 AND is_distributor = 0 AND DATABASEPROPERTYEX([name], ''Updateability'') = ''READ_WRITE'')
	BEGIN
	USE [?];
	SELECT TOP (' + CONVERT(nvarchar(max), @TopPerDB) + N')
	DB_NAME() AS DatabaseName,
	OBJECT_SCHEMA_NAME(i.object_id) AS SchemaName,
	OBJECT_NAME(i.object_id) AS TableName,
	i.name AS IndexName,
	i.fill_factor,
	DB_ID(), i.object_id, i.index_id,
	MAX(p.data_compression) AS CompressionType,
	SUM(p.rows) AS RowCounts,
	ROUND(SUM(a.total_pages) / 128.0, 2) AS TotalSpaceMB,
	ROUND(SUM(a.used_pages) / 128.0, 2) AS UsedSpaceMB,
	ROUND((SUM(a.total_pages) - SUM(a.used_pages)) / 128.0, 2) AS UnusedSpaceMB,
	MAX(us.user_seeks) AS user_seeks,
	MAX(us.user_scans) AS user_scans,
	MAX(us.user_lookups) AS user_lookups,
	MAX(us.user_updates) AS user_updates
	FROM
	sys.indexes i
	INNER JOIN
	sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
	INNER JOIN
	sys.allocation_units a ON p.partition_id = a.container_id
	LEFT JOIN
	sys.dm_db_index_usage_stats AS us ON us.database_id = DB_ID() AND us.object_id = i.object_id AND us.index_id = i.index_id
	WHERE
	OBJECT_NAME(i.object_id) NOT LIKE ''dt%''
	AND OBJECT_SCHEMA_NAME(i.object_id) <> ''sys''
	AND i.object_id > 255
	and p.rows >= 1
	GROUP BY
	i.object_id, i.name, i.index_id, i.fill_factor
	HAVING
	SUM(p.rows) >= ' + CONVERT(nvarchar(max), @MinimumRowCount) + N'
	AND (SUM(a.total_pages) - SUM(a.used_pages)) / 128 >= ' + CONVERT(nvarchar(max), @MinimumUnusedSizeMB) + N'
	AND (SUM(a.used_pages) * 1.0) / SUM(a.total_pages) <= 1 - (' + CONVERT(nvarchar(max), @MinimumUnusedSpacePct) + ' / 100.0)
	ORDER BY TotalSpaceMB DESC
	END'

	INSERT INTO @TempResult
	EXEC sp_MSforeachdb @command

	SELECT r.*
	, UnusedSpacePercent = UnusedSpaceMB / TotalSpaceMB * 100
	--, frg.avg_fragmentation_in_percent, frg.page_count
	--, frg.avg_page_space_used_in_percent
	, RebuildCommand = N'USE ' + QUOTENAME(DatabaseName) + N'; ' +
	CASE WHEN IndexName IS NULL THEN N'ALTER TABLE ' + QUOTENAME(SchemaName) + '.' + QUOTENAME(TableName)
	ELSE N'ALTER INDEX ' + QUOTENAME(IndexName) + N' ON ' + QUOTENAME(SchemaName) + '.' + QUOTENAME(TableName)
	END + N' REBUILD WITH (FILLFACTOR = ' + CONVERT(nvarchar(max), ISNULL(NULLIF(Fill_Factor,0),100)) + ISNULL(N', ' + NULLIF(@RebuildIndexOptions,N''), N'') + N');'
	, ReorganizeCommand = N'USE ' + QUOTENAME(DatabaseName) + N'; ' +
	CASE WHEN IndexName IS NULL THEN N'ALTER TABLE ' + QUOTENAME(SchemaName) + '.' + QUOTENAME(TableName)
	ELSE N'ALTER INDEX ' + QUOTENAME(IndexName) + N' ON ' + QUOTENAME(SchemaName) + '.' + QUOTENAME(TableName)
	END + N' REORGANIZE;'
	, CleanTableCommand = N'USE ' + QUOTENAME(DatabaseName) + N'; DBCC CLEANTABLE ('
	+ QUOTENAME(DatabaseName, N'''') + N', ' + QUOTENAME(QUOTENAME(SchemaName) + N'.' + QUOTENAME(TableName), N'''')
	+ N', 10000 ) WITH NO_INFOMSGS;'
	FROM @TempResult AS r
	--OUTER APPLY sys.dm_db_index_physical_stats(DatabaseID, ObjectId, IndexId, NULL, 'LIMITED') AS frg
	ORDER BY UnusedSpaceMB DESC
	OPTION (MAXDOP 1)

Change Tracking Utility function and procedure to maintain last copied version

2021-07-24T12:48:53Z

extended properties change tracking wrapper functions.sql

	CREATE FUNCTION dbo.ct_get_last_copied_version
	(
	@table_name sysname,
	@target_identifier sysname = null -- optional parameter if you have multiple sync targets
	)
	RETURNS bigint
	AS
	BEGIN
	RETURN ISNULL((SELECT convert(bigint, [value])
	FROM sys.extended_properties
	WHERE major_id = OBJECT_ID(@table_name)
	AND [name] = N'CT_LastCopiedVersion' + ISNULL(N'_' + @target_identifier, N'')), 0)
	END
	GO
	CREATE PROCEDURE dbo.ct_set_last_copied_version
	@table_name sysname,
	@new_version bigint = 0,
	@target_identifier sysname = null -- optional parameter if you have multiple sync targets
	AS
	SET NOCOUNT ON;

	DECLARE @PropertyName sysname, @PropertyValue sql_variant, @Level0Schema sysname, @Level1Table sysname;

	SET @PropertyName = N'CT_LastCopiedVersion' + ISNULL(N'_' + @target_identifier, N'')
	SET @Level0Schema = OBJECT_SCHEMA_NAME(OBJECT_ID(@table_name))
	SET @Level1Table = OBJECT_NAME(OBJECT_ID(@table_name))
	SET @PropertyValue = CONVERT(sql_variant, ISNULL(@new_version, 0))

	IF @Level0Schema IS NULL OR @Level1Table IS NULL RAISERROR(N'Specified table "%s" was not found.',16,1,@table_name);

	IF NOT EXISTS
	(
	SELECT *
	FROM sys.extended_properties
	WHERE major_id = OBJECT_ID(@table_name)
	AND [name] = @PropertyName
	)
	BEGIN
	EXEC sp_addextendedproperty @name = @PropertyName, @value = @PropertyValue,
	@level0type = N'SCHEMA', @level0name = @Level0Schema,
	@level1type = N'TABLE', @level1name = @Level1Table;
	END
	ELSE
	BEGIN
	EXEC sp_updateextendedproperty @name = @PropertyName, @value = @PropertyValue,
	@level0type = N'SCHEMA', @level0name = @Level0Schema,
	@level1type = N'TABLE', @level1name = @Level1Table;
	END
	GO

Function and stored procedure to implement Global Variables using Extended Properties

2021-07-24T12:25:23Z

extended properties global variable wrapper functions.sql

	-- Function to Retrieve a global variable value
	-- don't forget to convert to the correct data type
	CREATE FUNCTION dbo.global_variable(@VariableName sysname)
	RETURNS sql_variant
	AS
	BEGIN
	RETURN (SELECT [value]
	FROM sys.extended_properties
	WHERE major_id = 0 AND minor_id = 0
	AND [name] = @VariableName)
	END
	GO
	-- Stored Procedure to Save a global variable value
	CREATE PROCEDURE dbo.global_variable_set
	@VariableName sysname,
	@NewValue sql_variant
	AS
	SET NOCOUNT ON;
	IF NOT EXISTS
	(
	SELECT *
	FROM sys.extended_properties
	WHERE major_id = 0 AND minor_id = 0
	AND [name] = @VariableName
	)
	BEGIN
	EXEC sp_addextendedproperty @name = @VariableName, @value = @NewValue;
	END
	ELSE
	BEGIN
	EXEC sp_updateextendedproperty @name = @VariableName, @value = @NewValue;
	END
	GO

Asynchronous Ledger Demo

2021-07-12T20:46:43Z

Asynchronous Ledger Demo.sql

	/*
	Asynchronous Ledger Demo
	========================
	Author: Eitan Blumin \| https://eitanblumin.com \| https://madeiradata.com
	Date: 2021-07-03
	Description:
	This script demonstrates a use case of a high-throughput table
	which serves as a "hot-spot" for inserts and updates and queries.
	This causes performance problems due to long lock chains, possible deadlocks,
	and sometimes even worker thread starvation.

	The script then creates a solution for this problem in the form of
	an "Asynchronous Ledger" method utilizing ALTER TABLE SWITCH TO while
	separating between a "buffer" and a "staging" table, to eliminate any
	contention between the high-throughput INSERTs and any other operations.

	More details: https://eitanblumin.com/?p=2204
	*/

	/*************************************************/
	/************ Synchronous Demo ***************/
	/*************************************************/
	IF OBJECT_ID('MyHotTable') IS NOT NULL DROP TABLE MyHotTable;
	GO
	CREATE TABLE MyHotTable (
	SomeIdentifier int NOT NULL,
	SomeOtherIdentifier int NOT NULL,
	SomeText nchar(100) NULL,
	SomeOtherText nchar(100) NULL,
	SomeCounter int NOT NULL,
	CONSTRAINT PK_MyHotTable PRIMARY KEY CLUSTERED (SomeIdentifier, SomeOtherIdentifier)
	);

	CREATE NONCLUSTERED INDEX IX_MyHotTable_SomeOtherIdentifier ON MyHotTable (SomeOtherIdentifier) INCLUDE (SomeCounter, SomeText, SomeOtherText);
	GO
	WITH Level1
	AS
	(
	SELECT TOP (100) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
	FROM sys.all_columns a CROSS JOIN sys.all_columns b
	),
	Level2
	AS
	(
	SELECT TOP (10000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
	FROM sys.all_columns a CROSS JOIN sys.all_columns b
	)
	INSERT INTO MyHotTable (SomeIdentifier, SomeOtherIdentifier, SomeText, SomeOtherText, SomeCounter)
	SELECT Level1.n, Level2.n, NEWID(), NEWID(), ABS(CHECKSUM(NEWID())) % 10000
	FROM Level1 CROSS JOIN Level2
	GO
	CREATE OR ALTER PROCEDURE UpsertHotTable
	@SomeIdentifier int,
	@SomeOtherIdentifier int
	AS
	SET NOCOUNT ON;
	UPDATE MyHotTable SET SomeCounter = SomeCounter + 1
	WHERE SomeIdentifier = @SomeIdentifier AND SomeOtherIdentifier = @SomeOtherIdentifier

	IF @@ROWCOUNT = 0
	BEGIN
	INSERT INTO MyHotTable (SomeIdentifier, SomeOtherIdentifier, SomeText, SomeOtherText, SomeCounter)
	VALUES (@SomeIdentifier, @SomeOtherIdentifier, NEWID(), NEWID(), 1)
	END
	GO
	-- UPSERT thread:
	DECLARE
	@SomeIdentifier int = ABS(CHECKSUM(NEWID())) % 2 + 100
	, @SomeOtherIdentifier int = ABS(CHECKSUM(NEWID())) % 5 + 10000

	EXEC UpsertHotTable @SomeIdentifier, @SomeOtherIdentifier
	GO

	-- Query thread:
	IF OBJECT_ID('tempdb..#AggTemp') IS NULL
	CREATE TABLE #AggTemp
	(
	SomeIdentifier int,
	TotalCounter int
	);

	BEGIN TRAN

	INSERT INTO #AggTemp
	SELECT SomeIdentifier, SUM(SomeCounter)
	FROM MyHotTable WITH(REPEATABLEREAD)
	GROUP BY SomeIdentifier

	WAITFOR DELAY '00:00:03'

	DELETE FROM #AggTemp

	COMMIT
	GO



	/*************************************************/
	/*********** Asynchronous Demo ***************/
	/*************************************************/
	IF OBJECT_ID('MyHotTable_Buffer') IS NOT NULL DROP TABLE MyHotTable_Buffer;
	GO
	CREATE TABLE MyHotTable_Buffer (
	SomeIdentifier int NOT NULL,
	SomeOtherIdentifier int NOT NULL,
	SomeText nchar(100) NULL,
	SomeOtherText nchar(100) NULL,
	SomeCounter int NOT NULL
	);
	GO
	IF OBJECT_ID('MyHotTable_Staging') IS NOT NULL DROP TABLE MyHotTable_Staging;
	GO
	CREATE TABLE MyHotTable_Staging (
	SomeIdentifier int NOT NULL,
	SomeOtherIdentifier int NOT NULL,
	SomeText nchar(100) NULL,
	SomeOtherText nchar(100) NULL,
	SomeCounter int NOT NULL
	);
	GO
	CREATE OR ALTER PROCEDURE UpsertHotTable
	@SomeIdentifier int,
	@SomeOtherIdentifier int
	AS
	SET NOCOUNT ON;

	INSERT INTO MyHotTable_Buffer (SomeIdentifier, SomeOtherIdentifier, SomeText, SomeOtherText, SomeCounter)
	VALUES (@SomeIdentifier, @SomeOtherIdentifier, NEWID(), NEWID(), 1)
	GO
	CREATE OR ALTER PROCEDURE [dbo].[sp_MyHotTable_AsyncInsert]
	@DelayBeforeRepeat VARCHAR(17) = '00:00:15'
	AS
	BEGIN
	SET NOCOUNT, XACT_ABORT, ARITHABORT, QUOTED_IDENTIFIER ON;
	DECLARE @PreexistingData BIT = 0

	RestartPoint:

	IF EXISTS (SELECT * FROM dbo.MyHotTable_Staging)
	BEGIN
	SET @PreexistingData = 1;
	GOTO AggregateMerge;
	END

	TruncateAndSwitch:

	TRUNCATE TABLE dbo.MyHotTable_Staging;
	ALTER TABLE dbo.MyHotTable_Buffer SWITCH TO dbo.MyHotTable_Staging;

	AggregateMerge:
	DECLARE @RCount int, @DateString nvarchar(25);

	; WITH Trgt
	AS
	(
	SELECT *
	FROM dbo.MyHotTable AS prod WITH(UPDLOCK, HOLDLOCK)
	WHERE EXISTS
	(
	SELECT 1
	FROM MyHotTable_Staging AS stg
	WHERE stg.SomeIdentifier = prod.SomeIdentifier
	AND stg.SomeOtherIdentifier = prod.SomeOtherIdentifier
	)
	)
	MERGE INTO Trgt
	USING (
	SELECT SomeIdentifier, SomeOtherIdentifier, SUM(SomeCounter) AS SomeCounter
	FROM MyHotTable_Staging
	GROUP BY SomeIdentifier, SomeOtherIdentifier
	) AS Src
	ON Src.SomeIdentifier = Trgt.SomeIdentifier
	AND Src.SomeOtherIdentifier = Trgt.SomeOtherIdentifier
	WHEN MATCHED THEN
	UPDATE SET SomeCounter = Trgt.SomeCounter + Src.SomeCounter
	WHEN NOT MATCHED BY TARGET THEN
	INSERT (SomeIdentifier, SomeOtherIdentifier, SomeText, SomeOtherText, SomeCounter)
	VALUES (Src.SomeIdentifier, Src.SomeOtherIdentifier, NEWID(), NEWID(), Src.SomeCounter)
	;

	SET @RCount = @@ROWCOUNT;
	SET @DateString = CONVERT(nvarchar(25), GETDATE(), 121)

	IF @RCount > 0 RAISERROR(N'%s - Loaded %d row(s)',0,1,@DateString,@RCount) WITH NOWAIT;

	TRUNCATE TABLE dbo.MyHotTable_Staging;

	IF @PreexistingData = 1
	BEGIN
	SET @PreexistingData = 0;
	GOTO RestartPoint;
	END

	-- Check for new data
	IF EXISTS (SELECT TOP 1 1 FROM dbo.MyHotTable_Buffer WITH(READPAST))
	BEGIN
	GOTO RestartPoint;
	END
	ELSE IF @DelayBeforeRepeat IS NOT NULL
	BEGIN
	WAITFOR DELAY @DelayBeforeRepeat;

	IF EXISTS (SELECT TOP 1 1 FROM dbo.MyHotTable_Buffer WITH(READPAST))
	BEGIN
	GOTO RestartPoint;
	END
	END
	END
	GO

	SET XACT_ABORT, ARITHABORT ON;

	IF NOT EXISTS (SELECT * FROM msdb..sysjobs WHERE name = N'MyHotTable_AsyncInsert')
	BEGIN

	BEGIN TRANSACTION
	DECLARE @ReturnCode INT
	SELECT @ReturnCode = 0
	IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
	BEGIN
	EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
	IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
	END

	DECLARE @jobId BINARY(16)
	EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'MyHotTable_AsyncInsert',
	@enabled=1,
	@notify_level_eventlog=0,
	@notify_level_email=0,
	@notify_level_netsend=0,
	@notify_level_page=0,
	@delete_level=0,
	@description=N'No description available.',
	@category_name=N'[Uncategorized (Local)]',
	@owner_login_name=N'sa', @job_id = @jobId OUTPUT
	IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
	EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'MyHotTable_AsyncInsert',
	@step_id=1,
	@cmdexec_success_code=0,
	@on_success_action=1,
	@on_success_step_id=0,
	@on_fail_action=2,
	@on_fail_step_id=0,
	@retry_attempts=0,
	@retry_interval=0,
	@os_run_priority=0, @subsystem=N'TSQL',
	@command=N'EXEC [dbo].[sp_MyHotTable_AsyncInsert]',
	@database_name=N'MyDB',
	@flags=4
	IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
	EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
	IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
	EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Every 1 minute',
	@enabled=1,
	@freq_type=4,
	@freq_interval=1,
	@freq_subday_type=4,
	@freq_subday_interval=1,
	@freq_relative_interval=0,
	@freq_recurrence_factor=0,
	@active_start_date=20210615,
	@active_end_date=99991231,
	@active_start_time=0,
	@active_end_time=235959
	IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
	EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
	IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
	COMMIT TRANSACTION
	GOTO EndSave
	QuitWithRollback:
	IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
	EndSave:

	END
	GO

Use Extended Properties as Database Global Variables

2021-06-14T07:29:55Z

Extended Properties as Database Global Variables.sql

	/*
	================================================
	Extended Properties as Database Global Variables
	================================================
	Author: Eitan Blumin \| https://madeiradata.com \| https://eitanblumin.com
	Date: 2021-06-04
	Description:
	Use this sample script as a template or starting point
	for when you want to utilize extended properties
	to save and retrieve values as if using "global" variables
	at the database level.
	*/
	DECLARE @PreviousValue datetime
	, @NewValue datetime
	, @ExtendedPropertyName sysname = N'My_Database_Global_Var'

	-- Retrieve a value: (don't forget to convert to the correct data type)
	SELECT @PreviousValue = CONVERT(datetime, [value])
	FROM sys.extended_properties
	WHERE [name] = @ExtendedPropertyName

	/* TODO: do something here with @PreviousValue and @NewValue */

	-- Save a value:
	IF NOT EXISTS
	(
	SELECT *
	FROM sys.extended_properties
	WHERE [name] = @ExtendedPropertyName
	)
	BEGIN
	EXEC sp_addextendedproperty @name = @ExtendedPropertyName, @value = @NewValue;
	END
	ELSE
	BEGIN
	EXEC sp_updateextendedproperty @name = @ExtendedPropertyName, @value = @NewValue;
	END

sp_help_revlogin2 is a simpler alternative to sp_help_revlogin

2021-05-05T19:36:20Z

sp_help_revlogin2.sql

	IF OBJECT_ID('tempdb..#sp_help_revlogin2') IS NOT NULL DROP PROCEDURE #sp_help_revlogin2
	GO
	/*********************************************************************************************
	sp_help_revlogin2 V1.2
	Eitan Blumin

	https://eitanblumin.com \| https://madeiradata.com
	https://gist.github.com/EitanBlumin/1f19b0b3f59a9220641c559653b90f15
	https://github.com/MadeiraData/MadeiraToolbox/blob/master/Utility%20Scripts/sp_help_revlogin2.sql
	https://eitanblumin.com/2021/05/11/t-sql-tuesday-138-sp_help_revlogin-is-dead-long-live-sp_help_revlogin2/

	This is a simpler alternative to sp_help_revlogin.

	Standard disclaimer: You use scripts off of the web at your own risk. I fully expect this
	script to work without issue but I've been known to be wrong before.

	Parameters:
	@login_name
	Optionally filter for a specific login name. Defaults to NULL (all logins).

	@include_system_logins
	If set to 1, will output system principals such as sa, NT SERVICE accounts, and ##... accounts.

	@command_separator
	By default equals to 'GO'. Will be used as a separator between each CREATE LOGIN command.
	*********************************************************************************************
	-- V1.2
	-- 14/12/2021 - added support for Azure SQL DB

	-- V1.1
	-- 23/06/2021 - added new optional parameter @login_name

	-- V1.0
	-- 05/05/2021
	*********************************************************************************************/
	CREATE PROCEDURE #sp_help_revlogin2
	@login_name sysname = NULL,
	@include_system_logins bit = 0,
	@command_separator nvarchar(1000) = N'GO'
	AS
	SET NOCOUNT, ARITHABORT, XACT_ABORT, QUOTED_IDENTIFIER ON;

	DECLARE @Output AS TABLE (Content NVARCHAR(MAX));
	PRINT N'
	/***************************************************/
	/* sp_help_revlogin2 output */
	/***************************************************/
	-- Generated on: ' + CONVERT(nvarchar(25), GETDATE(),121)

	IF CONVERT(int, SERVERPROPERTY('EngineEdition')) <> 5 AND OBJECT_ID('sys.server_principals') IS NOT NULL
	BEGIN
	PRINT N'-- Generating from: sys.server_principals'

	INSERT INTO @Output
	SELECT
	+ CHAR(13) + CHAR(10) + N'-- Login: ' + [name] + CHAR(13) + CHAR(10)
	+ CASE WHEN type IN ( 'G', 'U')
	THEN N'CREATE LOGIN ' + QUOTENAME( login_name ) + CHAR(13) + CHAR(10) + ' FROM WINDOWS WITH DEFAULT_DATABASE = ' + QUOTENAME( ISNULL(default_database_name, DB_NAME()) )
	ELSE N'CREATE LOGIN ' + QUOTENAME( login_name ) + CHAR(13) + CHAR(10) + ' WITH PASSWORD = ' + CONVERT(nvarchar(max), CAST( LOGINPROPERTY( login_name, 'PasswordHash' ) AS varbinary (max)), 1)
	+ ' HASHED, SID = ' + CONVERT(nvarchar(max), [sid], 1) + CHAR(13) + CHAR(10) + ', DEFAULT_DATABASE = ' + QUOTENAME( ISNULL(default_database_name, DB_NAME()) )
	END
	+ CASE WHEN CAST(LOGINPROPERTY( login_name, 'HistoryLength' ) AS int) <> 0 THEN N', CHECK_POLICY = ON' ELSE N'' END
	+ CASE WHEN LOGINPROPERTY( login_name, 'DaysUntilExpiration' ) IS NOT NULL THEN N', CHECK_EXPIRATION = ON' ELSE N'' END
	+ N';'
	+ CASE WHEN dp.is_disabled = 1 THEN CHAR(13) + CHAR(10) + N'ALTER LOGIN ' + QUOTENAME( login_name ) + N' DISABLE;' ELSE N'' END
	FROM sys.server_principals AS dp
	CROSS APPLY ( SELECT [name] AS login_name ) AS l
	WHERE [sid] IS NOT NULL
	AND type IN ( 'S', 'G', 'U' )
	AND (@login_name IS NULL OR @login_name = l.login_name)
	AND (
	@include_system_logins = 1
	OR ([sid] NOT IN (0x00, 0x01) AND [name] NOT LIKE N'##%##' AND [name] NOT LIKE N'NT SERVICE\%' AND [name] NOT LIKE N'NT AUTHORITY\%')
	)
	END


	IF OBJECT_ID('sys.sql_logins') IS NOT NULL AND CONVERT(int, SERVERPROPERTY('EngineEdition')) = 5
	BEGIN
	PRINT N'-- Generating from: sys.sql_logins'

	INSERT INTO @Output
	SELECT
	+ CHAR(13) + CHAR(10) + N'-- Login: ' + [name] + CHAR(13) + CHAR(10)
	+ CASE WHEN type IN ( 'G', 'U')
	THEN N'CREATE LOGIN ' + QUOTENAME( login_name ) + CHAR(13) + CHAR(10) + ' FROM WINDOWS WITH DEFAULT_DATABASE = ' + QUOTENAME( ISNULL(default_database_name, DB_NAME()) )
	ELSE N'CREATE LOGIN ' + QUOTENAME( login_name ) + CHAR(13) + CHAR(10) + ' WITH PASSWORD = ' + CONVERT(nvarchar(max), dp.password_hash, 1)
	+ ' HASHED, SID = ' + CONVERT(nvarchar(max), [sid], 1) + CHAR(13) + CHAR(10) + ', DEFAULT_DATABASE = ' + QUOTENAME( ISNULL(default_database_name, DB_NAME()) )
	END
	+ CASE WHEN CAST(LOGINPROPERTY( login_name, 'HistoryLength' ) AS int) <> 0 THEN N', CHECK_POLICY = ON' ELSE N'' END
	+ CASE WHEN LOGINPROPERTY( login_name, 'DaysUntilExpiration' ) IS NOT NULL THEN N', CHECK_EXPIRATION = ON' ELSE N'' END
	+ N';'
	+ CASE WHEN dp.is_disabled = 1 THEN CHAR(13) + CHAR(10) + N'ALTER LOGIN ' + QUOTENAME( login_name ) + N' DISABLE;' ELSE N'' END
	FROM sys.sql_logins AS dp
	CROSS APPLY ( SELECT [name] AS login_name ) AS l
	WHERE [sid] IS NOT NULL
	AND type IN ( 'S', 'G', 'U' )
	AND (@login_name IS NULL OR @login_name = l.login_name)
	AND (
	@include_system_logins = 1
	OR ([sid] NOT IN (0x00, 0x01) AND [name] NOT LIKE N'##%##' AND [name] NOT LIKE N'NT SERVICE\%' AND [name] NOT LIKE N'NT AUTHORITY\%')
	)
	END
	ELSE IF CONVERT(int, SERVERPROPERTY('EngineEdition')) = 5
	BEGIN
	RAISERROR(N'This script does not support Azure SQL User Databases. You must run this from the "master" database.',16,1);
	END

	IF CONVERT(int, SERVERPROPERTY('EngineEdition')) <> 5 AND NOT EXISTS (SELECT NULL FROM @Output)
	BEGIN
	PRINT N'-- Generating from: sys.database_principals'

	INSERT INTO @Output
	SELECT
	+ N'-- Login: ' + [name] + CHAR(13) + CHAR(10)
	+ CASE WHEN type IN ( 'G', 'U')
	THEN N'CREATE LOGIN ' + QUOTENAME( [name] ) + CHAR(13) + CHAR(10) + ' FROM WINDOWS WITH DEFAULT_DATABASE = ' + QUOTENAME( ISNULL(CONVERT(sysname, LOGINPROPERTY( [name], 'DefaultDatabase')), DB_NAME()) )
	ELSE N'CREATE LOGIN ' + QUOTENAME( [name] ) + CHAR(13) + CHAR(10) + ' WITH PASSWORD = ' + CONVERT(nvarchar(max), CAST( LOGINPROPERTY( [name], 'PasswordHash' ) AS varbinary (max)), 1)
	+ ' HASHED, SID = ' + CONVERT(nvarchar(max), [sid], 1) + CHAR(13) + CHAR(10) + ', DEFAULT_DATABASE = ' + QUOTENAME( ISNULL(CONVERT(sysname, LOGINPROPERTY( [name], 'DefaultDatabase')), DB_NAME()) )
	END
	+ CASE WHEN CAST(LOGINPROPERTY( [name], 'HistoryLength' ) AS int) <> 0 THEN N', CHECK_POLICY = ON' ELSE N'' END
	+ CASE WHEN LOGINPROPERTY( [name], 'DaysUntilExpiration' ) IS NOT NULL THEN N', CHECK_EXPIRATION = ON' ELSE N'' END
	+ N';'
	--, UserCreateScript = N'CREATE USER ' + QUOTENAME([name]) + N' FOR LOGIN ' + QUOTENAME( [name] ) + N';'
	FROM sys.database_principals AS dp
	WHERE [sid] IS NOT NULL
	AND type IN ( 'S', 'G', 'U' )
	AND (@login_name IS NULL OR @login_name = [name])
	AND (
	@include_system_logins = 1
	OR ([sid] NOT IN (0x00, 0x01) AND [name] NOT LIKE N'##%##')
	)
	END

	DECLARE @Content NVARCHAR(MAX)

	DECLARE Outputs CURSOR
	LOCAL FAST_FORWARD
	FOR
	SELECT Content FROM @Output

	OPEN Outputs

	WHILE 1=1
	BEGIN
	FETCH NEXT FROM Outputs INTO @Content;
	IF @@FETCH_STATUS <> 0
	BREAK;

	PRINT ISNULL(@command_separator, CHAR(13) + CHAR(10))
	PRINT @Content;
	END

	CLOSE Outputs
	DEALLOCATE Outputs

	PRINT ISNULL(@command_separator, CHAR(13) + CHAR(10))
	GO


	EXEC #sp_help_revlogin2
	-- @include_system_logins = 1

sp_DBPermissions

2021-02-18T19:31:11Z

sp_DBPermissions.sql

	IF OBJECT_ID('tempdb..#sp_DBPermissions') IS NOT NULL DROP PROCEDURE #sp_DBPermissions
	GO
	/*********************************************************************************************
	sp_DBPermissions V6.1
	Kenneth Fisher

	http://www.sqlstudies.com
	https://github.com/sqlstudent144/SQL-Server-Scripts/blob/master/sp_DBPermissions.sql

	This stored procedure returns 3 data sets. The first dataset is the list of database
	principals, the second is role membership, and the third is object and database level
	permissions.

	The final 2 columns of each query are "Un-Do"/"Do" scripts. For example removing a member
	from a role or adding them to a role. I am fairly confident in the role scripts, however,
	the scripts in the database principals query and database/object permissions query are
	works in progress. In particular certificates, keys and column level permissions are not
	scripted out. Also while the scripts have worked flawlessly on the systems I've tested
	them on, these systems are fairly similar when it comes to security so I can't say that
	in a more complicated system there won't be the odd bug.

	Standard disclaimer: You use scripts off of the web at your own risk. I fully expect this
	script to work without issue but I've been known to be wrong before.

	Parameters:
	@DBName
	If NULL use the current database, otherwise give permissions based on the parameter.

	There is a special case where you pass in ALL to the @DBName. In this case the SP
	will loop through (yes I'm using a cursor) all of the DBs in sysdatabases and run
	the queries into temp tables before returning the results. WARNINGS: If you use
	this option and have a large number of databases it will be SLOW. If you use this
	option and don't specify any other parameters (say a specific @Principal) and have
	even a medium number of databases it will be SLOW. Also the undo/do scripts do
	not have USE statements in them so please take that into account.
	@Principal
	If NOT NULL then all three queries only pull for that database principal. @Principal
	is a pattern check. The queries check for any row where the passed in value exists.
	It uses the pattern '%' + @Principal + '%'
	@Role
	If NOT NULL then the roles query will pull members of the role. If it is NOT NULL and
	@DBName is NULL then DB principal and permissions query will pull the principal row for
	the role and the permissions for the role. @Role is a pattern check. The queries
	check for any row where the passed in value exists. It uses the pattern '%' + @Role +
	'%'
	@Type
	If NOT NULL then all three queries will only pull principals of that type.
	S = SQL login
	U = Windows login
	G = Windows group
	R = Server role
	C = Login mapped to a certificate
	K = Login mapped to an asymmetric key
	@ObjectName
	If NOT NULL then the third query will display permissions specific to the object
	specified and the first two queries will display only those users with those specific
	permissions. Unfortunately at this point only objects in sys.all_objects will work.
	This parameter uses the pattern '%' + @ObjectName + '%'
	@Permission
	If NOT NULL then the third query will display only permissions that match what is in
	the parameter. The first two queries will display only those users with that specific
	permission.
	@LoginName
	If NOT NULL then each of the queries will only pull back database principals that
	have the same SID as a login that matches the pattern '%' + @LoginName + '%'
	@UseLikeSearch
	When this is set to 1 (the default) then the search parameters will use LIKE (and
	%'s will be added around the @Principal, @Role, @ObjectName, and @LoginName parameters).
	When set to 0 searchs will use =.
	@IncludeMSShipped
	When this is set to 1 (the default) then all principals will be included. When set
	to 0 the fixed server roles and SA and Public principals will be excluded.
	@DropTempTables
	When this is set to 1 (the default) the temp tables used are dropped. If it's 0
	then the tempt ables are kept for references after the code has finished.
	The temp tables are:
	##DBPrincipals
	##DBRoles
	##DBPermissions
	@Output
	What type of output is desired.
	Default - Either 'Default' or it doesn't match any of the allowed values then the SP
	will return the standard 3 outputs.
	None - No output at all. Usually used if you keeping the temp tables to do your own
	reporting.
	CreateOnly - Only return the create scripts where they aren't NULL.
	DropOnly - Only return the drop scripts where they aren't NULL.
	ScriptsOnly - Return drop and create scripts where they aren't NULL.
	Report - Returns one output with one row per principal and a comma delimited list of
	roles the principal is a member of and a comma delimited list of the
	individual permissions they have.
	@Print
	Defaults to 0, but if a 1 is passed in then the queries are not run but printed
	out instead. This is primarily for debugging.

	Data is ordered as follows
	1st result set: DBPrincipal
	2nd result set: RoleName, UserName if the parameter @Role is used else
	UserName, RoleName
	3rd result set: If @ObjectName is used then DBName, SchemaName, ObjectName, Grantee_Name, permission_name
	otherwise DBName, GranteeName, SchemaName, ObjectName, permission_name

	-- V2.0
	-- 8/18/2013 – Create a stub if the SP doesn’t exist, then always do an alter
	-- 8/18/2013 - Use instance collation for all concatenated strings
	-- 9/04/2013 - dbo can’t be added or removed from roles. Don’t script.
	-- 9/04/2013 - Fix scripts for schema level permissions.
	-- 9/04/2013 – Change print option to show values of variables not the
	-- Variable names.
	-- V3.0
	-- 10/5/2013 - Added @Type parameter to pull only principals of a given type.
	-- 10/10/2013 - Added @ObjectName parameter to pull only permissions for a given object.
	-- V4.0
	-- 11/18/2013 - Added parameter names to sp_addrolemember and sp_droprolemember.
	-- 11/19/2013 - Added an ORDER BY to each of the result sets. See above for details.
	-- 01/04/2014 - Add an ALL option to the DBName parameter.
	-- V4.1
	-- 02/07/2014 - Fix bug scripting permissions where object and schema have the same ID
	-- 02/15/2014 - Add support for user defined types
	-- 02/15/2014 - Fix: Add schema to object GRANT and REVOKE scripts
	-- V5.0
	-- 4/29/2014 - Fix: Removed extra print statements
	-- 4/29/2014 - Fix: Added SET NOCOUNT ON
	-- 4/29/2014 - Added a USE statement to the scripts when using the @DBName = 'All' option
	-- 5/01/2014 - Added @Permission parameter
	-- 5/14/2014 - Added additional permissions based on information from Kendal Van Dyke's
	post http://www.kendalvandyke.com/2014/02/using-sysobjects-when-scripting.html
	-- 6/02/2014 - Added @LoginName parameter
	-- V5.5
	-- 7/15/2014 - Bunch of changes recommended by @SQLSoldier/"https://twitter.com/SQLSoldier"
	Primarily changing the strings to unicode & adding QUOTENAME in a few places
	I'd missed it.
	-- V6.0
	-- 10/19/2014 - Add @UserLikeSearch and @IncludeMSShipped parameters.
	-- 11/29/2016 - Fixed permissions for symmetric keys
	-- Found and fixed by Brenda Grossnickle
	-- 03/25/2017 - Move SID towards the end of the first output so the more important
	-- columns are closer to the front.
	-- 03/25/2017 - Add IF Exists to drop and create user scripts
	-- 03/25/2017 - Remove create/drop user scripts for guest, public, sys and INFORMATION_SCHEMA
	-- 03/25/2017 - Add @DropTempTables to keep the temp tables after the SP is run.
	-- 03/26/2017 - Add @Output to allow different types of output.
	-- V6.1
	-- 06/25/2018 - Skip snapshots
	-- 02/13/2019 - Fix to direct permissions column in the report output to show schema permissions correctly
	-- 04/05/2019 - For 'All' DB parameter fix to only look at ONLINE and EMERGENCY DBs.
	-- 06/04/2019 - Add SchemaName and permission_name to the order of the third data set.
	This makes the order more reliable.
	-- 06/04/2019 - Begin cleanup of the dynamic SQL (specifically removing carrage return & extra quotes)
	-- 06/04/2019 - Fix @print where part of the permissions query was being truncated.
	*********************************************************************************************/
	CREATE PROCEDURE #sp_DBPermissions
	(
	@DBName sysname = NULL,
	@Principal sysname = NULL,
	@Role sysname = NULL,
	@Type nvarchar(30) = NULL,
	@ObjectName sysname = NULL,
	@Permission sysname = NULL,
	@LoginName sysname = NULL,
	@UseLikeSearch bit = 1,
	@IncludeMSShipped bit = 1,
	@DropTempTables bit = 1,
	@Output varchar(30) = 'Default',
	@Print bit = 0
	)
	AS

	SET NOCOUNT ON

	DECLARE @Collation nvarchar(75)
	SET @Collation = N' COLLATE ' + CAST(SERVERPROPERTY('Collation') AS nvarchar(50))

	DECLARE @sql nvarchar(max)
	DECLARE @sql2 nvarchar(max)
	DECLARE @ObjectList nvarchar(max)
	DECLARE @ObjectList2 nvarchar(max)
	DECLARE @use nvarchar(500)
	DECLARE @AllDBNames sysname

	IF @DBName IS NULL OR @DBName = N'All'
	BEGIN
	SET @use = ''
	IF @DBName IS NULL
	SET @DBName = DB_NAME()
	--SELECT @DBName = db_name(database_id)
	--FROM sys.dm_exec_requests
	--WHERE session_id = @@SPID
	END
	ELSE
	-- IF EXISTS (SELECT 1 FROM sys.databases WHERE name = @DBName)
	IF db_id(@DBName) IS NOT NULL
	SET @use = N'USE ' + QUOTENAME(@DBName) + N';' + NCHAR(13)
	ELSE
	BEGIN
	RAISERROR (N'%s is not a valid database name.',
	16,
	1,
	@DBName)
	RETURN
	END

	DECLARE @LikeOperator nvarchar(4)

	IF @UseLikeSearch = 1
	SET @LikeOperator = N'LIKE'
	ELSE
	SET @LikeOperator = N'='

	IF @UseLikeSearch = 1
	BEGIN
	IF LEN(ISNULL(@Principal,'')) > 0
	SET @Principal = N'%' + @Principal + N'%'

	IF LEN(ISNULL(@Role,'')) > 0
	SET @Role = N'%' + @Role + N'%'

	IF LEN(ISNULL(@ObjectName,'')) > 0
	SET @ObjectName = N'%' + @ObjectName + N'%'

	IF LEN(ISNULL(@LoginName,'')) > 0
	SET @LoginName = N'%' + @LoginName + N'%'
	END

	IF @Print = 1 AND @DBName = N'All'
	BEGIN
	PRINT 'DECLARE @AllDBNames sysname'
	PRINT 'SET @AllDBNames = ''master'''
	PRINT ''
	END
	--=========================================================================
	-- Database Principals
	SET @sql =
	N'SELECT ' + CASE WHEN @DBName = 'All' THEN N'@AllDBNames' ELSE N'''' + @DBName + N'''' END + N' AS DBName,' +
	N' DBPrincipals.principal_id AS DBPrincipalId, DBPrincipals.name AS DBPrincipal, SrvPrincipals.name AS SrvPrincipal, ' + NCHAR(13) +
	N' DBPrincipals.type, DBPrincipals.type_desc, DBPrincipals.default_schema_name, DBPrincipals.create_date, ' + NCHAR(13) +
	N' DBPrincipals.modify_date, DBPrincipals.is_fixed_role, ' + NCHAR(13) +
	N' Authorizations.name AS RoleAuthorization, DBPrincipals.sid, ' + NCHAR(13) +
	N' CASE WHEN DBPrincipals.is_fixed_role = 0 AND DBPrincipals.name NOT IN (''dbo'',''guest'', ''INFORMATION_SCHEMA'', ''public'', ''sys'') THEN ' + NCHAR(13) +
	CASE WHEN @DBName = 'All' THEN N' ''USE '' + QUOTENAME(@AllDBNames) + ''; '' + ' + NCHAR(13) ELSE N'' END +
	N' ''IF DATABASE_PRINCIPAL_ID('''''' + DBPrincipals.name + '''''') IS NOT NULL '' + ' + NCHAR(13) +
	N' ''DROP '' + CASE DBPrincipals.[type] WHEN ''C'' THEN NULL ' + NCHAR(13) +
	N' WHEN ''K'' THEN NULL ' + NCHAR(13) +
	N' WHEN ''R'' THEN ''ROLE'' ' + NCHAR(13) +
	N' WHEN ''A'' THEN ''APPLICATION ROLE'' ' + NCHAR(13) +
	N' ELSE ''USER'' END + ' + NCHAR(13) +
	N' '' ''+QUOTENAME(DBPrincipals.name' + @Collation + N') + '';'' ELSE NULL END AS DropScript, ' + NCHAR(13) +
	N' CASE WHEN DBPrincipals.is_fixed_role = 0 AND DBPrincipals.name NOT IN (''dbo'',''guest'', ''INFORMATION_SCHEMA'', ''public'', ''sys'') THEN ' + NCHAR(13) +
	CASE WHEN @DBName = 'All' THEN N' ''USE '' + QUOTENAME(@AllDBNames) + ''; '' + ' +NCHAR(13) ELSE N'' END +
	N' ''IF DATABASE_PRINCIPAL_ID('''''' + DBPrincipals.name + '''''') IS NULL '' + ' + NCHAR(13) +
	N' ''CREATE '' + CASE DBPrincipals.[type] WHEN ''C'' THEN NULL ' + NCHAR(13) +
	N' WHEN ''K'' THEN NULL ' + NCHAR(13) +
	N' WHEN ''R'' THEN ''ROLE'' ' + NCHAR(13) +
	N' WHEN ''A'' THEN ''APPLICATION ROLE'' ' + NCHAR(13) +
	N' ELSE ''USER'' END + ' + NCHAR(13) +
	N' '' ''+QUOTENAME(DBPrincipals.name' + @Collation + N') END + ' + NCHAR(13) +
	N' CASE WHEN DBPrincipals.[type] = ''R'' THEN ' + NCHAR(13) +
	N' ISNULL('' AUTHORIZATION ''+QUOTENAME(Authorizations.name' + @Collation + N'),'''') ' + NCHAR(13) +
	N' WHEN DBPrincipals.[type] = ''A'' THEN ' + NCHAR(13) +
	N' '''' ' + NCHAR(13) +
	N' WHEN DBPrincipals.[type] NOT IN (''C'',''K'') THEN ' + NCHAR(13) +
	N' ISNULL('' FOR LOGIN '' +
	QUOTENAME(SrvPrincipals.name' + @Collation + N'),'' WITHOUT LOGIN'') + ' + NCHAR(13) +
	N' ISNULL('' WITH DEFAULT_SCHEMA = ''+
	QUOTENAME(DBPrincipals.default_schema_name' + @Collation + N'),'''') ' + NCHAR(13) +
	N' ELSE '''' ' + NCHAR(13) +
	N' END + '';'' + ' + NCHAR(13) +
	N' CASE WHEN DBPrincipals.[type] NOT IN (''C'',''K'',''R'',''A'') ' + NCHAR(13) +
	N' AND SrvPrincipals.name IS NULL ' + NCHAR(13) +
	N' AND DBPrincipals.sid IS NOT NULL ' + NCHAR(13) +
	N' AND DBPrincipals.sid NOT IN (0x00, 0x01) ' + NCHAR(13) +
	N' THEN '' -- Possible missing server principal'' ' + NCHAR(13) +
	N' ELSE '''' END ' + NCHAR(13) +
	N' AS CreateScript ' + NCHAR(13) +
	N'FROM sys.database_principals DBPrincipals ' + NCHAR(13) +
	N'LEFT OUTER JOIN sys.database_principals Authorizations ' + NCHAR(13) +
	N' ON DBPrincipals.owning_principal_id = Authorizations.principal_id ' + NCHAR(13) +
	N'LEFT OUTER JOIN sys.server_principals SrvPrincipals ' + NCHAR(13) +
	N' ON DBPrincipals.sid = SrvPrincipals.sid ' + NCHAR(13) +
	N' AND DBPrincipals.sid NOT IN (0x00, 0x01) ' + NCHAR(13) +
	N'WHERE 1=1 '

	IF LEN(ISNULL(@Principal,@Role)) > 0
	IF @Print = 1
	SET @sql = @sql + NCHAR(13) + N' AND DBPrincipals.name ' + @LikeOperator + N' ' +
	ISNULL(QUOTENAME(@Principal,N''''),QUOTENAME(@Role,''''))
	ELSE
	SET @sql = @sql + NCHAR(13) + N' AND DBPrincipals.name ' + @LikeOperator + N' ISNULL(@Principal,@Role) '

	IF LEN(@Type) > 0
	IF @Print = 1
	SET @sql = @sql + NCHAR(13) + N' AND DBPrincipals.type ' + @LikeOperator + N' ' + QUOTENAME(@Type,'''')
	ELSE
	SET @sql = @sql + NCHAR(13) + N' AND DBPrincipals.type ' + @LikeOperator + N' @Type'

	IF LEN(@LoginName) > 0
	IF @Print = 1
	SET @sql = @sql + NCHAR(13) + N' AND SrvPrincipals.name ' + @LikeOperator + N' ' + QUOTENAME(@LoginName,'''')
	ELSE
	SET @sql = @sql + NCHAR(13) + N' AND SrvPrincipals.name ' + @LikeOperator + N' @LoginName'

	IF LEN(@ObjectName) > 0
	BEGIN
	SET @sql = @sql + NCHAR(13) +
	N' AND EXISTS (SELECT 1 ' + NCHAR(13) +
	N' FROM sys.all_objects [Objects] ' + NCHAR(13) +
	N' INNER JOIN sys.database_permissions Permission ' + NCHAR(13) +
	N' ON Permission.major_id = [Objects].object_id ' + NCHAR(13) +
	N' WHERE Permission.major_id = [Objects].object_id ' + NCHAR(13) +
	N' AND Permission.grantee_principal_id = DBPrincipals.principal_id ' + NCHAR(13)

	IF @Print = 1
	SET @sql = @sql + N' AND [Objects].name ' + @LikeOperator + N' ' + QUOTENAME(@ObjectName,'''')
	ELSE
	SET @sql = @sql + N' AND [Objects].name ' + @LikeOperator + N' @ObjectName'

	SET @sql = @sql + N')'
	END

	IF LEN(@Permission) > 0
	BEGIN
	SET @sql = @sql + NCHAR(13) +
	N' AND EXISTS (SELECT 1 ' + NCHAR(13) +
	N' FROM sys.database_permissions Permission ' + NCHAR(13) +
	N' WHERE Permission.grantee_principal_id = DBPrincipals.principal_id ' + NCHAR(13)

	IF @Print = 1
	SET @sql = @sql + N' AND Permission.permission_name ' + @LikeOperator + N' ' + QUOTENAME(@Permission,'''')
	ELSE
	SET @sql = @sql + N' AND Permission.permission_name ' + @LikeOperator + N' @Permission'

	SET @sql = @sql + N')'
	END

	IF @IncludeMSShipped = 0
	SET @sql = @sql + NCHAR(13) + N' AND DBPrincipals.is_fixed_role = 0 ' + NCHAR(13) +
	' AND DBPrincipals.name NOT IN (''dbo'',''public'',''INFORMATION_SCHEMA'',''guest'',''sys'') '

	IF @Print = 1
	BEGIN
	PRINT N'-- Database Principals'
	PRINT CAST(@sql AS nvarchar(max))
	PRINT '' -- Spacing before the next print
	PRINT ''
	END
	ELSE
	BEGIN
	IF object_id('tempdb..##DBPrincipals') IS NOT NULL
	DROP TABLE ##DBPrincipals

	-- Create temp table to store the data in
	CREATE TABLE ##DBPrincipals (
	DBName sysname NULL,
	DBPrincipalId int NULL,
	DBPrincipal sysname NULL,
	SrvPrincipal sysname NULL,
	type char(1) NULL,
	type_desc nchar(60) NULL,
	default_schema_name sysname NULL,
	create_date datetime NULL,
	modify_date datetime NULL,
	is_fixed_role bit NULL,
	RoleAuthorization sysname NULL,
	sid varbinary(85) NULL,
	DropScript nvarchar(max) NULL,
	CreateScript nvarchar(max) NULL
	)

	SET @sql = @use + N'INSERT INTO ##DBPrincipals ' + NCHAR(13) + @sql

	IF @DBName = 'All'
	BEGIN
	-- Declare a READ_ONLY cursor to loop through the databases
	DECLARE cur_DBList CURSOR
	READ_ONLY
	FOR SELECT name FROM sys.databases
	WHERE state IN (0,5)
	AND source_database_id IS NULL
	ORDER BY name

	OPEN cur_DBList

	FETCH NEXT FROM cur_DBList INTO @AllDBNames
	WHILE (@@fetch_status <> -1)
	BEGIN
	IF (@@fetch_status <> -2)
	BEGIN
	SET @sql2 = N'USE ' + QUOTENAME(@AllDBNames) + N';' + NCHAR(13) + @sql
	EXEC sp_executesql @sql2,
	N'@Principal sysname, @Role sysname, @Type nvarchar(30), @ObjectName sysname,
	@AllDBNames sysname, @Permission sysname, @LoginName sysname',
	@Principal, @Role, @Type, @ObjectName, @AllDBNames, @Permission, @LoginName
	-- PRINT @sql2
	END
	FETCH NEXT FROM cur_DBList INTO @AllDBNames
	END

	CLOSE cur_DBList
	DEALLOCATE cur_DBList
	END
	ELSE
	EXEC sp_executesql @sql, N'@Principal sysname, @Role sysname, @Type nvarchar(30),
	@ObjectName sysname, @Permission sysname, @LoginName sysname',
	@Principal, @Role, @Type, @ObjectName, @Permission, @LoginName
	END
	--=========================================================================
	-- Database Role Members
	SET @sql =
	N'SELECT ' + CASE WHEN @DBName = 'All' THEN N'@AllDBNames' ELSE N'''' + @DBName + N'''' END + N' AS DBName,' +
	N' Users.principal_id AS UserPrincipalId, Users.name AS UserName, Roles.name AS RoleName, ' + NCHAR(13) +
	CASE WHEN @DBName = 'All' THEN N' ''USE '' + QUOTENAME(@AllDBNames) + ''; '' + ' + NCHAR(13) ELSE N'' END +
	N' CASE WHEN Users.is_fixed_role = 0 AND Users.name <> ''dbo'' THEN ' + NCHAR(13) +
	N' ''EXEC sp_droprolemember @rolename = ''+QUOTENAME(Roles.name' + @Collation +
	N','''''''')+'', @membername = ''+QUOTENAME(CASE WHEN Users.name = ''dbo'' THEN NULL
	ELSE Users.name END' + @Collation +
	N','''''''')+'';'' END AS DropScript, ' + NCHAR(13) +
	CASE WHEN @DBName = 'All' THEN N' ''USE '' + QUOTENAME(@AllDBNames) + ''; '' + ' + NCHAR(13) ELSE N'' END +
	N' CASE WHEN Users.is_fixed_role = 0 AND Users.name <> ''dbo'' THEN ' + NCHAR(13) +
	N' ''EXEC sp_addrolemember @rolename = ''+QUOTENAME(Roles.name' + @Collation +
	N','''''''')+'', @membername = ''+QUOTENAME(CASE WHEN Users.name = ''dbo'' THEN NULL
	ELSE Users.name END' + @Collation +
	N','''''''')+'';'' END AS AddScript ' + NCHAR(13) +
	N'FROM sys.database_role_members RoleMembers ' + NCHAR(13) +
	N'JOIN sys.database_principals Users ' + NCHAR(13) +
	N' ON RoleMembers.member_principal_id = Users.principal_id ' + NCHAR(13) +
	N'JOIN sys.database_principals Roles ' + NCHAR(13) +
	N' ON RoleMembers.role_principal_id = Roles.principal_id ' + NCHAR(13) +
	N'WHERE 1=1 '

	IF LEN(ISNULL(@Principal,'')) > 0
	IF @Print = 1
	SET @sql = @sql + NCHAR(13) + N' AND Users.name ' + @LikeOperator + N' '+QUOTENAME(@Principal,'''')
	ELSE
	SET @sql = @sql + NCHAR(13) + N' AND Users.name ' + @LikeOperator + N' @Principal'

	IF LEN(ISNULL(@Role,'')) > 0
	IF @Print = 1
	SET @sql = @sql + NCHAR(13) + N' AND Roles.name ' + @LikeOperator + N' '+QUOTENAME(@Role,'''')
	ELSE
	SET @sql = @sql + NCHAR(13) + N' AND Roles.name ' + @LikeOperator + N' @Role'

	IF LEN(@Type) > 0
	IF @Print = 1
	SET @sql = @sql + NCHAR(13) + N' AND Users.type ' + @LikeOperator + N' ' + QUOTENAME(@Type,'''')
	ELSE
	SET @sql = @sql + NCHAR(13) + N' AND Users.type ' + @LikeOperator + N' @Type'

	IF LEN(@LoginName) > 0
	BEGIN
	SET @sql = @sql + NCHAR(13) +
	N' AND EXISTS (SELECT 1 ' + NCHAR(13) +
	N' FROM sys.server_principals SrvPrincipals ' + NCHAR(13) +
	N' WHERE Users.sid NOT IN (0x00, 0x01) ' + NCHAR(13) +
	N' AND SrvPrincipals.sid = Users.sid ' + NCHAR(13) +
	N' AND Users.type NOT IN (''R'') ' + NCHAR(13)
	IF @Print = 1
	SET @sql = @sql + NCHAR(13) + ' AND SrvPrincipals.name ' + @LikeOperator + N' ' + QUOTENAME(@LoginName,'''')
	ELSE
	SET @sql = @sql + NCHAR(13) + ' AND SrvPrincipals.name ' + @LikeOperator + N' @LoginName'

	SET @sql = @sql + N')'
	END

	IF LEN(@ObjectName) > 0
	BEGIN
	SET @sql = @sql + NCHAR(13) +
	N' AND EXISTS (SELECT 1 ' + NCHAR(13) +
	N' FROM sys.all_objects [Objects] ' + NCHAR(13) +
	N' INNER JOIN sys.database_permissions Permission ' + NCHAR(13) +
	N' ON Permission.major_id = [Objects].object_id ' + NCHAR(13) +
	N' WHERE Permission.major_id = [Objects].object_id ' + NCHAR(13) +
	N' AND Permission.grantee_principal_id = Users.principal_id ' + NCHAR(13)

	IF @Print = 1
	SET @sql = @sql + N' AND [Objects].name ' + @LikeOperator + N' ' + QUOTENAME(@ObjectName,'''')
	ELSE
	SET @sql = @sql + N' AND [Objects].name ' + @LikeOperator + N' @ObjectName'

	SET @sql = @sql + N')'
	END

	IF LEN(@Permission) > 0
	BEGIN
	SET @sql = @sql + NCHAR(13) +
	N' AND EXISTS (SELECT 1 ' + NCHAR(13) +
	N' FROM sys.database_permissions Permission ' + NCHAR(13) +
	N' WHERE Permission.grantee_principal_id = Users.principal_id ' + NCHAR(13)

	IF @Print = 1
	SET @sql = @sql + N' AND Permission.permission_name ' + @LikeOperator + N' ' + QUOTENAME(@Permission,'''')
	ELSE
	SET @sql = @sql + N' AND Permission.permission_name ' + @LikeOperator + N' @Permission'

	SET @sql = @sql + N')'
	END

	IF @IncludeMSShipped = 0
	SET @sql = @sql + NCHAR(13) + N' AND Users.is_fixed_role = 0 ' + NCHAR(13) +
	' AND Users.name NOT IN (''dbo'',''public'',''INFORMATION_SCHEMA'',''guest'',''sys'') '

	IF @Print = 1
	BEGIN
	PRINT N'-- Database Role Members'
	PRINT CAST(@sql AS nvarchar(max))
	PRINT '' -- Spacing before the next print
	PRINT ''
	END
	ELSE
	BEGIN
	IF object_id('tempdb..##DBRoles') IS NOT NULL
	DROP TABLE ##DBRoles

	-- Create temp table to store the data in
	CREATE TABLE ##DBRoles (
	DBName sysname NULL,
	UserPrincipalId int NULL,
	UserName sysname NULL,
	RoleName sysname NULL,
	DropScript nvarchar(max) NULL,
	AddScript nvarchar(max) NULL
	)

	SET @sql = @use + NCHAR(13) + 'INSERT INTO ##DBRoles ' + NCHAR(13) + @sql

	IF @DBName = 'All'
	BEGIN
	-- Declare a READ_ONLY cursor to loop through the databases
	DECLARE cur_DBList CURSOR
	READ_ONLY
	FOR SELECT name FROM sys.databases
	WHERE state IN (0,5)
	AND source_database_id IS NULL
	ORDER BY name

	OPEN cur_DBList

	FETCH NEXT FROM cur_DBList INTO @AllDBNames
	WHILE (@@fetch_status <> -1)
	BEGIN
	IF (@@fetch_status <> -2)
	BEGIN
	SET @sql2 = 'USE ' + QUOTENAME(@AllDBNames) + ';' + NCHAR(13) + @sql
	EXEC sp_executesql @sql2,
	N'@Principal sysname, @Role sysname, @Type nvarchar(30), @ObjectName sysname,
	@AllDBNames sysname, @Permission sysname, @LoginName sysname',
	@Principal, @Role, @Type, @ObjectName, @AllDBNames, @Permission, @LoginName
	-- PRINT @sql2
	END
	FETCH NEXT FROM cur_DBList INTO @AllDBNames
	END

	CLOSE cur_DBList
	DEALLOCATE cur_DBList
	END
	ELSE
	EXEC sp_executesql @sql, N'@Principal sysname, @Role sysname, @Type nvarchar(30),
	@ObjectName sysname, @Permission sysname, @LoginName sysname',
	@Principal, @Role, @Type, @ObjectName, @Permission, @LoginName
	END

	--=========================================================================
	-- Database & object Permissions
	SET @ObjectList =
	N'; WITH ObjectList AS (
	SELECT NULL AS SchemaName ,
	name ' + @Collation + ' AS name,
	database_id AS id,
	''DATABASE'' AS class_desc,
	'''' AS class
	FROM master.sys.databases
	UNION ALL
	SELECT SCHEMA_NAME(sys.all_objects.schema_id) ' + @Collation + N' AS SchemaName,
	name ' + @Collation + N' AS name,
	object_id AS id,
	''OBJECT_OR_COLUMN'' AS class_desc,
	''OBJECT'' AS class
	FROM sys.all_objects
	UNION ALL
	SELECT name ' + @Collation + N' AS SchemaName,
	NULL AS name,
	schema_id AS id,
	''SCHEMA'' AS class_desc,
	''SCHEMA'' AS class
	FROM sys.schemas
	UNION ALL
	SELECT NULL AS SchemaName,
	name ' + @Collation + N' AS name,
	principal_id AS id,
	''DATABASE_PRINCIPAL'' AS class_desc,
	CASE type_desc
	WHEN ''APPLICATION_ROLE'' THEN ''APPLICATION ROLE''
	WHEN ''DATABASE_ROLE'' THEN ''ROLE''
	ELSE ''USER'' END AS class
	FROM sys.database_principals
	UNION ALL
	SELECT SCHEMA_NAME(schema_id) ' + @Collation + N' AS SchemaName,
	name ' + @Collation + N' AS name,
	xml_collection_id AS id,
	''XML_SCHEMA_COLLECTION'' AS class_desc,
	''XML SCHEMA COLLECTION'' AS class
	FROM sys.xml_schema_collections
	UNION ALL
	SELECT NULL AS SchemaName,
	name ' + @Collation + N' AS name,
	message_type_id AS id,
	''MESSAGE_TYPE'' AS class_desc,
	''MESSAGE TYPE'' AS class
	FROM sys.service_message_types
	UNION ALL
	SELECT NULL AS SchemaName,
	name ' + @Collation + N' AS name,
	assembly_id AS id,
	''ASSEMBLY'' AS class_desc,
	''ASSEMBLY'' AS class
	FROM sys.assemblies
	UNION ALL'

	SET @ObjectList2 = N'
	SELECT SCHEMA_NAME(sys.types.schema_id) ' + @Collation + N' AS SchemaName,
	name ' + @Collation + N' AS name,
	user_type_id AS id,
	''TYPE'' AS class_desc,
	''TYPE'' AS class
	FROM sys.types
	UNION ALL
	SELECT NULL AS SchemaName,
	name ' + @Collation + N' AS name,
	service_contract_id AS id,
	''SERVICE_CONTRACT'' AS class_desc,
	''CONTRACT'' AS class
	FROM sys.service_contracts
	UNION ALL
	SELECT NULL AS SchemaName,
	name ' + @Collation + N' AS name,
	service_id AS id,
	''SERVICE'' AS class_desc,
	''SERVICE'' AS class
	FROM sys.services
	UNION ALL
	SELECT NULL AS SchemaName,
	name ' + @Collation + N' AS name,
	remote_service_binding_id AS id,
	''REMOTE_SERVICE_BINDING'' AS class_desc,
	''REMOTE SERVICE BINDING'' AS class
	FROM sys.remote_service_bindings
	UNION ALL
	SELECT NULL AS SchemaName,
	name ' + @Collation + N' AS name,
	route_id AS id,
	''ROUTE'' AS class_desc,
	''ROUTE'' AS class
	FROM sys.routes
	UNION ALL
	SELECT NULL AS SchemaName,
	name ' + @Collation + N' AS name,
	fulltext_catalog_id AS id,
	''FULLTEXT_CATALOG'' AS class_desc,
	''FULLTEXT CATALOG'' AS class
	FROM sys.fulltext_catalogs
	UNION ALL
	SELECT NULL AS SchemaName,
	name ' + @Collation + N' AS name,
	symmetric_key_id AS id,
	''SYMMETRIC_KEYS'' AS class_desc,
	''SYMMETRIC KEY'' AS class
	FROM sys.symmetric_keys
	UNION ALL
	SELECT NULL AS SchemaName,
	name ' + @Collation + N' AS name,
	certificate_id AS id,
	''CERTIFICATE'' AS class_desc,
	''CERTIFICATE'' AS class
	FROM sys.certificates
	UNION ALL
	SELECT NULL AS SchemaName,
	name ' + @Collation + N' AS name,
	asymmetric_key_id AS id,
	''ASYMMETRIC_KEY'' AS class_desc,
	''ASYMMETRIC KEY'' AS class
	FROM sys.asymmetric_keys
	) ' + NCHAR(13)

	SET @sql =
	N'SELECT ' + CASE WHEN @DBName = 'All' THEN N'@AllDBNames' ELSE N'''' + @DBName + N'''' END + N' AS DBName,' + NCHAR(13) +
	N' Grantee.principal_id AS GranteePrincipalId, Grantee.name AS GranteeName, Grantor.name AS GrantorName, ' + NCHAR(13) +
	N' Permission.class_desc, Permission.permission_name, ' + NCHAR(13) +
	N' ObjectList.name AS ObjectName, ' + NCHAR(13) +
	N' ObjectList.SchemaName, ' + NCHAR(13) +
	N' Permission.state_desc, ' + NCHAR(13) +
	N' CASE WHEN Grantee.is_fixed_role = 0 AND Grantee.name <> ''dbo'' THEN ' + NCHAR(13) +
	CASE WHEN @DBName = 'All' THEN N' ''USE '' + QUOTENAME(@AllDBNames) + ''; '' + ' + NCHAR(13) ELSE N'' END +
	N' ''REVOKE '' + ' + NCHAR(13) +
	N' CASE WHEN Permission.[state] = ''W'' THEN ''GRANT OPTION FOR '' ELSE '''' END + ' + NCHAR(13) +
	N' '' '' + Permission.permission_name' + @Collation + N' + ' + NCHAR(13) +
	N' CASE WHEN Permission.major_id <> 0 THEN '' ON '' + ' + NCHAR(13) +
	N' ObjectList.class + ''::'' + ' + NCHAR(13) +
	N' ISNULL(QUOTENAME(ObjectList.SchemaName),'''') + ' + NCHAR(13) +
	N' CASE WHEN ObjectList.SchemaName + ObjectList.name IS NULL THEN '''' ELSE ''.'' END + ' + NCHAR(13) +
	N' ISNULL(QUOTENAME(ObjectList.name),'''') ' + NCHAR(13) +
	N' ' + @Collation + ' + '' '' ELSE '''' END + ' + NCHAR(13) +
	N' '' FROM '' + QUOTENAME(Grantee.name' + @Collation + N') + ''; '' END AS RevokeScript, ' + NCHAR(13) +
	N' CASE WHEN Grantee.is_fixed_role = 0 AND Grantee.name <> ''dbo'' THEN ' + NCHAR(13) +
	CASE WHEN @DBName = 'All' THEN N' ''USE '' + QUOTENAME(@AllDBNames) + ''; '' + ' + NCHAR(13) ELSE N'' END +
	N' CASE WHEN Permission.[state] = ''W'' THEN ''GRANT'' ELSE Permission.state_desc' + @Collation +
	N' END + ' + NCHAR(13) +
	N' '' '' + Permission.permission_name' + @Collation + N' + ' + NCHAR(13) +
	N' CASE WHEN Permission.major_id <> 0 THEN '' ON '' + ' + NCHAR(13) +
	N' ObjectList.class + ''::'' + ' + NCHAR(13) +
	N' ISNULL(QUOTENAME(ObjectList.SchemaName),'''') + ' + NCHAR(13) +
	N' CASE WHEN ObjectList.SchemaName + ObjectList.name IS NULL THEN '''' ELSE ''.'' END + ' + NCHAR(13) +
	N' ISNULL(QUOTENAME(ObjectList.name),'''') ' + NCHAR(13) +
	N' ' + @Collation + N' + '' '' ELSE '''' END + ' + NCHAR(13) +
	N' '' TO '' + QUOTENAME(Grantee.name' + @Collation + N') + '' '' + ' + NCHAR(13) +
	N' CASE WHEN Permission.[state] = ''W'' THEN '' WITH GRANT OPTION '' ELSE '''' END + ' + NCHAR(13) +
	N' '' AS ''+ QUOTENAME(Grantor.name' + @Collation + N')+'';'' END AS GrantScript ' + NCHAR(13) +
	N'FROM sys.database_permissions Permission ' + NCHAR(13) +
	N'JOIN sys.database_principals Grantee ' + NCHAR(13) +
	N' ON Permission.grantee_principal_id = Grantee.principal_id ' + NCHAR(13) +
	N'JOIN sys.database_principals Grantor ' + NCHAR(13) +
	N' ON Permission.grantor_principal_id = Grantor.principal_id ' + NCHAR(13) +
	N'LEFT OUTER JOIN ObjectList ' + NCHAR(13) +
	N' ON Permission.major_id = ObjectList.id ' + NCHAR(13) +
	N' AND Permission.class_desc = ObjectList.class_desc ' + NCHAR(13) +
	N'WHERE 1=1 '

	IF LEN(ISNULL(@Principal,@Role)) > 0
	IF @Print = 1
	SET @sql = @sql + NCHAR(13) + N' AND Grantee.name ' + @LikeOperator + N' ' + ISNULL(QUOTENAME(@Principal,''''),QUOTENAME(@Role,''''))
	ELSE
	SET @sql = @sql + NCHAR(13) + N' AND Grantee.name ' + @LikeOperator + N' ISNULL(@Principal,@Role) '

	IF LEN(@Type) > 0
	IF @Print = 1
	SET @sql = @sql + NCHAR(13) + N' AND Grantee.type ' + @LikeOperator + N' ' + QUOTENAME(@Type,'''')
	ELSE
	SET @sql = @sql + NCHAR(13) + N' AND Grantee.type ' + @LikeOperator + N' @Type'

	IF LEN(@ObjectName) > 0
	IF @Print = 1
	SET @sql = @sql + NCHAR(13) + N' AND ObjectList.name ' + @LikeOperator + N' ' + QUOTENAME(@ObjectName,'''')
	ELSE
	SET @sql = @sql + NCHAR(13) + N' AND ObjectList.name ' + @LikeOperator + N' @ObjectName '

	IF LEN(@Permission) > 0
	IF @Print = 1
	SET @sql = @sql + NCHAR(13) + N' AND Permission.permission_name ' + @LikeOperator + N' ' + QUOTENAME(@Permission,'''')
	ELSE
	SET @sql = @sql + NCHAR(13) + N' AND Permission.permission_name ' + @LikeOperator + N' @Permission'

	IF LEN(@LoginName) > 0
	BEGIN
	SET @sql = @sql + NCHAR(13) +
	N' AND EXISTS (SELECT 1 ' + NCHAR(13) +
	N' FROM sys.server_principals SrvPrincipals ' + NCHAR(13) +
	N' WHERE SrvPrincipals.sid = Grantee.sid ' + NCHAR(13) +
	N' AND Grantee.sid NOT IN (0x00, 0x01) ' + NCHAR(13) +
	N' AND Grantee.type NOT IN (''R'') ' + NCHAR(13)
	IF @Print = 1
	SET @sql = @sql + NCHAR(13) + N' AND SrvPrincipals.name ' + @LikeOperator + N' ' + QUOTENAME(@LoginName,'''')
	ELSE
	SET @sql = @sql + NCHAR(13) + N' AND SrvPrincipals.name ' + @LikeOperator + N' @LoginName'

	SET @sql = @sql + ')'
	END

	IF @IncludeMSShipped = 0
	SET @sql = @sql + NCHAR(13) + N' AND Grantee.is_fixed_role = 0 ' + NCHAR(13) +
	' AND Grantee.name NOT IN (''dbo'',''public'',''INFORMATION_SCHEMA'',''guest'',''sys'') '

	IF @Print = 1
	BEGIN
	PRINT '-- Database & object Permissions'
	PRINT CAST(@use AS nvarchar(max))
	PRINT CAST(@ObjectList AS nvarchar(max))
	PRINT CAST(@ObjectList2 AS nvarchar(max))
	PRINT CAST(@sql AS nvarchar(max))
	END
	ELSE
	BEGIN
	IF object_id('tempdb..##DBPermissions') IS NOT NULL
	DROP TABLE ##DBPermissions

	-- Create temp table to store the data in
	CREATE TABLE ##DBPermissions (
	DBName sysname NULL,
	GranteePrincipalId int NULL,
	GranteeName sysname NULL,
	GrantorName sysname NULL,
	class_desc nvarchar(60) NULL,
	permission_name nvarchar(128) NULL,
	ObjectName sysname NULL,
	SchemaName sysname NULL,
	state_desc nvarchar(60) NULL,
	RevokeScript nvarchar(max) NULL,
	GrantScript nvarchar(max) NULL
	)

	-- Add insert statement to @sql
	SET @sql = @use + @ObjectList + @ObjectList2 +
	N'INSERT INTO ##DBPermissions ' + NCHAR(13) +
	@sql

	IF @DBName = 'All'
	BEGIN
	-- Declare a READ_ONLY cursor to loop through the databases
	DECLARE cur_DBList CURSOR
	READ_ONLY
	FOR SELECT name FROM sys.databases
	WHERE state IN (0,5)
	AND source_database_id IS NULL
	ORDER BY name

	OPEN cur_DBList

	FETCH NEXT FROM cur_DBList INTO @AllDBNames
	WHILE (@@fetch_status <> -1)
	BEGIN
	IF (@@fetch_status <> -2)
	BEGIN
	SET @sql2 = 'USE ' + QUOTENAME(@AllDBNames) + ';' + NCHAR(13) + @sql
	EXEC sp_executesql @sql2,
	N'@Principal sysname, @Role sysname, @Type nvarchar(30), @ObjectName sysname,
	@AllDBNames sysname, @Permission sysname, @LoginName sysname',
	@Principal, @Role, @Type, @ObjectName, @AllDBNames, @Permission, @LoginName
	-- PRINT @sql2
	END
	FETCH NEXT FROM cur_DBList INTO @AllDBNames
	END

	CLOSE cur_DBList
	DEALLOCATE cur_DBList
	END
	ELSE
	BEGIN
	EXEC sp_executesql @sql, N'@Principal sysname, @Role sysname, @Type nvarchar(30),
	@ObjectName sysname, @Permission sysname, @LoginName sysname',
	@Principal, @Role, @Type, @ObjectName, @Permission, @LoginName
	END
	END

	IF @Print <> 1
	BEGIN
	IF @Output = 'None'
	PRINT ''
	ELSE IF @Output = 'CreateOnly'
	BEGIN
	SELECT CreateScript FROM ##DBPrincipals WHERE CreateScript IS NOT NULL
	UNION ALL
	SELECT AddScript FROM ##DBRoles WHERE AddScript IS NOT NULL
	UNION ALL
	SELECT GrantScript FROM ##DBPermissions WHERE GrantScript IS NOT NULL
	END
	ELSE IF @Output = 'DropOnly'
	BEGIN
	SELECT DropScript FROM ##DBPrincipals WHERE DropScript IS NOT NULL
	UNION ALL
	SELECT DropScript FROM ##DBRoles WHERE DropScript IS NOT NULL
	UNION ALL
	SELECT RevokeScript FROM ##DBPermissions WHERE RevokeScript IS NOT NULL
	END
	ELSE IF @Output = 'ScriptOnly'
	BEGIN
	SELECT DropScript, CreateScript FROM ##DBPrincipals WHERE DropScript IS NOT NULL OR CreateScript IS NOT NULL
	UNION ALL
	SELECT DropScript, AddScript FROM ##DBRoles WHERE DropScript IS NOT NULL OR AddScript IS NOT NULL
	UNION ALL
	SELECT RevokeScript, GrantScript FROM ##DBPermissions WHERE RevokeScript IS NOT NULL OR GrantScript IS NOT NULL
	END
	ELSE IF @Output = 'Report'
	BEGIN
	SELECT DBName, DBPrincipal, SrvPrincipal, type, type_desc,
	STUFF((SELECT ', ' + ##DBRoles.RoleName
	FROM ##DBRoles
	WHERE ##DBPrincipals.DBName = ##DBRoles.DBName
	AND ##DBPrincipals.DBPrincipalId = ##DBRoles.UserPrincipalId
	ORDER BY ##DBRoles.RoleName
	FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)')
	, 1, 2, '') AS RoleMembership,
	STUFF((SELECT ', ' + ##DBPermissions.state_desc + ' ' + ##DBPermissions.permission_name + ' on ' +
	COALESCE('OBJECT:'+##DBPermissions.SchemaName + '.' + ##DBPermissions.ObjectName,
	'SCHEMA:'+##DBPermissions.SchemaName,
	'DATABASE:'+##DBPermissions.DBName)
	FROM ##DBPermissions
	WHERE ##DBPrincipals.DBName = ##DBPermissions.DBName
	AND ##DBPrincipals.DBPrincipalId = ##DBPermissions.GranteePrincipalId
	ORDER BY ##DBPermissions.state_desc, ISNULL(##DBPermissions.ObjectName, ##DBPermissions.DBName), ##DBPermissions.permission_name
	FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)')
	, 1, 2, '') AS DirectPermissions
	FROM ##DBPrincipals
	ORDER BY DBName, type, DBPrincipal
	END
	ELSE -- 'Default' or no match
	BEGIN
	SELECT DBName, DBPrincipal, SrvPrincipal, type, type_desc, default_schema_name,
	create_date, modify_date, is_fixed_role, RoleAuthorization, sid,
	DropScript, CreateScript
	FROM ##DBPrincipals ORDER BY DBName, DBPrincipal
	IF LEN(@Role) > 0
	SELECT DBName, UserName, RoleName, DropScript, AddScript
	FROM ##DBRoles ORDER BY DBName, RoleName, UserName
	ELSE
	SELECT DBName, UserName, RoleName, DropScript, AddScript
	FROM ##DBRoles ORDER BY DBName, UserName, RoleName

	IF LEN(@ObjectName) > 0
	SELECT DBName, GranteeName, GrantorName, class_desc, permission_name, ObjectName,
	SchemaName, state_desc, RevokeScript, GrantScript
	FROM ##DBPermissions ORDER BY DBName, SchemaName, ObjectName, GranteeName, permission_name
	ELSE
	SELECT DBName, GranteeName, GrantorName, class_desc, permission_name, ObjectName,
	SchemaName, state_desc, RevokeScript, GrantScript
	FROM ##DBPermissions ORDER BY DBName, GranteeName, SchemaName, ObjectName, permission_name
	END

	IF @DropTempTables = 1
	BEGIN
	DROP TABLE ##DBPrincipals
	DROP TABLE ##DBRoles
	DROP TABLE ##DBPermissions
	END
	END
	GO

	EXEC #sp_DBPermissions

sp_SrvPermissions

2021-02-18T19:30:40Z

sp_SrvPermissions.sql

	IF OBJECT_ID('#sp_SrvPermissions') IS NOT NULL DROP PROCEDURE #sp_SrvPermissions
	GO
	/*********************************************************************************************
	sp_SrvPermissions V6.1
	Kenneth Fisher

	http://www.sqlstudies.com
	https://github.com/sqlstudent144/SQL-Server-Scripts/blob/master/sp_SrvPermissions.sql

	This stored procedure returns 3 data sets. The first dataset is the list of server
	principals, the second is role membership, and the third is server level permissions.

	The final 2 columns of each query are "Un-Do"/"Do" scripts. For example removing a member
	from a role or adding them to a role. I am fairly confident in the role scripts, however,
	the scripts in the server principals query and server permissions query are works in
	progress. In particular certificates and keys are not scripted out. Also while the scripts
	have worked flawlessly on the systems I've tested them on, these systems are fairly similar
	when it comes to security so I can't say that in a more complicated system there won't be
	the odd bug.

	Notes on the create script for server principals:
	1) I have included a hashed version of the password and the sid. This means that when run
	on another server the password and the sid will remain the same.
	2) In SQL 2005 the create script on the server principals query DOES NOT WORK. This is
	because the conversion of the sid (in varbinary) to character doesn't appear to work
	as I expected in SQL 2005. It works fine in SQL 2008 and above. If you want to use
	this script in SQL 2005 you can change the CONVERTs in the principal script to
	master.sys.fn_varbintohexstr

	Standard disclaimer: You use scripts off of the web at your own risk. I fully expect this
	script to work without issue but I've been known to be wrong before.

	Parameters:
	@Principal
	If NOT NULL then all three queries only pull for that server principal. @Principal
	is a pattern check. The queries check for any row where the passed in value exists.
	It uses the pattern '%' + @Principal + '%'
	@Role
	If NOT NULL then the roles query will pull members of the role. If it is NOT NULL and
	@Principal is NULL then Server principal and permissions query will pull the principal
	row for the role and the permissions for the role. @Role is a pattern check. The
	queries check for any row where the passed in value exists. It uses the pattern
	'%' + @Role + '%'
	@Type
	If NOT NULL then all three queries will only pull principals of that type.
	S = SQL login
	U = Windows login
	G = Windows group
	R = Server role
	C = Login mapped to a certificate
	K = Login mapped to an asymmetric key
	@DBName
	If NOT NULL then only return those principals and information about them where the
	principal exists within the DB specified.
	@UseLikeSearch
	When this is set to 1 (the default) then the search parameters will use LIKE (and
	%'s will be added around the @Principal and @Role parameters).
	When set to 0 searchs will use =.
	@IncludeMSShipped
	When this is set to 1 (the default) then all principals will be included. When set
	to 0 the fixed server roles and SA and Public principals will be excluded.
	@DropTempTables
	When this is set to 1 (the default) the temp tables used are dropped. If it's 0
	then the tempt ables are kept for references after the code has finished.
	The temp tables are:
	##SrvPrincipals
	##SrvRoles
	##SrvPermissions
	@Output
	What type of output is desired.
	Default - Either 'Default' or it doesn't match any of the allowed values then the SP
	will return the standard 3 outputs.
	None - No output at all. Usually used if you keeping the temp tables to do your own
	reporting.
	CreateOnly - Only return the create scripts where they aren't NULL.
	DropOnly - Only return the drop scripts where they aren't NULL.
	ScriptsOnly - Return drop and create scripts where they aren't NULL.
	Report - Returns one output with one row per principal and a comma delimited list of
	roles the principal is a member of and a comma delimited list of the
	individual permissions they have.
	@Print
	Defaults to 0, but if a 1 is passed in then the queries are not run but printed
	out instead. This is primarily for debugging.

	Data is ordered as follows
	1st result set: SrvPrincipal
	2nd result set: RoleName, LoginName if the parameter @Role is used else
	LoginName, RoleName
	3rd result set: GranteeName

	*********************************************************************************************
	-- V2.0
	-- 8/18/2013 – Create a stub if the SP doesn’t exist, then always do an alter
	-- 9/04/2013 – Change print option to show values of variables not the
	-- Variable names.
	-- V3.0
	-- 10/5/2013 - Added @Type parameter to pull only principals of a given type.
	-- 10/20/2013 - Remove SID in CREATE LOGIN script from v2005 and lower since it requires
	a special function to convert from binary to varchar.
	-- V4.0
	-- 11/18/2013 - Corrected bug in the order of the parameters for sp_addsrvrolemember
	and sp_dropsrvrolemember, also added parameter names both.
	-- 01/09/2014 - Added an ORDER BY to each of the result sets. See above for details.
	-- V5.0
	-- 04/27/2014 - Add @DBName parameter
	-- V5.5
	-- 7/22/2014 - Changed strings to unicode
	-- V6.0
	-- 10/19/2014 - Add @UserLikeSearch and @IncludeMSShipped parameters.
	-- 03/25/2017 - Move SID towards the end of the first output so the more important
	-- columns are closer to the front.
	-- 03/25/2017 - Add IF Exists to drop and create principal scripts
	-- 03/25/2017 - Add @DropTempTables to keep the temp tables after the SP is run.
	-- 03/26/2017 - Add @Output to allow different types of output.
	-- V6.1
	-- 06/13/2018 - Removed scripts for principal IDs under 100 (anecdotally the system IDs)
	-- - Added SET NOCOUNT ON
	-- 05/28/2019 - Add scripts & mappings for certificate & asymmetric key mapped principals.
	-- - Start cleaning up the dynamic SQL a bit to make it easier to read.
	-- - Fix SERVER ROLE scripts
	-- - Add CHECK_POLICY and CHECK_EXPIRATION
	-- - Add script support for disabled
	-- - Add script support for a single credential. Will not support multiple credentials.
	*********************************************************************************************/
	CREATE PROCEDURE #sp_SrvPermissions
	(
	@Principal sysname = NULL,
	@Role sysname = NULL,
	@Type nvarchar(30) = NULL,
	@DBName sysname = NULL,
	@UseLikeSearch bit = 1,
	@IncludeMSShipped bit = 1,
	@DropTempTables bit = 1,
	@Output varchar(30) = 'Default',
	@Print bit = 0
	)
	AS

	SET NOCOUNT ON

	IF @DBName IS NOT NULL AND NOT EXISTS (SELECT 1 FROM sys.databases WHERE name = @DBName)
	BEGIN
	RAISERROR (N'%s is not a valid database name.',
	16,
	1,
	@DBName)
	RETURN
	END

	DECLARE @Collation nvarchar(50)
	SET @Collation = N' COLLATE ' + CAST(SERVERPROPERTY('Collation') AS nvarchar(50))

	DECLARE @Version2005orLower bit
	SELECT @Version2005orLower = CASE WHEN PARSENAME(CAST(SERVERPROPERTY('productversion') AS VARCHAR(20)),4) < 10 THEN 1
	ELSE 0 END

	DECLARE @sql nvarchar(max)
	DECLARE @LikeOperator nvarchar(4)

	IF @UseLikeSearch = 1
	SET @LikeOperator = N'LIKE'
	ELSE
	SET @LikeOperator = N'='

	IF @UseLikeSearch = 1
	BEGIN
	IF LEN(ISNULL(@Principal,'')) > 0
	SET @Principal = N'%' + @Principal + N'%'

	IF LEN(ISNULL(@Role,'')) > 0
	SET @Role = N'%' + @Role+ N'%'
	END

	--=========================================================================
	-- Server Principals
	SET @sql =
	N'SELECT Logins.principal_id AS SrvPrincipalId, Logins.name AS SrvPrincipal, Logins.type, Logins.type_desc,
	Logins.is_disabled, Logins.default_database_name, Logins.default_language_name,
	CASE sql_logins.is_policy_checked WHEN 1 THEN ''ON'' WHEN 0 THEN ''OFF'' END AS check_policy,
	CASE sql_logins.is_expiration_checked WHEN 1 THEN ''ON'' WHEN 0 THEN ''OFF'' END AS check_expiration,
	ISNULL(Cert.name,aKey.name) AS Cert_or_asymmetric_key,
	Logins.sid,
	CASE WHEN Logins.principal_id < 100 THEN NULL ELSE
	''IF EXISTS (SELECT * FROM sys.server_principals WHERE name = '' + QuoteName(Logins.name,'''''''') + '') '' +
	''DROP '' + CASE Logins.type
	WHEN ''R'' THEN ''SERVER ROLE''
	ELSE ''LOGIN'' END +
	'' ''+QUOTENAME(Logins.name' + @Collation + ') END + '';'' AS DropScript,
	CASE WHEN Logins.principal_id < 100 THEN NULL ELSE
	''IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = '' + QuoteName(Logins.name,'''''''') + '') '' +
	''CREATE '' + CASE Logins.type
	WHEN ''R'' THEN ''SERVER ROLE''
	ELSE ''LOGIN'' END +
	'' ''+QUOTENAME(Logins.name' + @Collation + ') END +
	CASE WHEN Logins.type = (''S'') THEN '' WITH PASSWORD = '' +
	CONVERT(varchar(256), LOGINPROPERTY(Logins.name, ''PasswordHash''),1 ) + '' HASHED' +
	CASE WHEN @Version2005orLower = 0 THEN N','' +
	'' SID = '' + CONVERT(varchar(85), Logins.sid, 1) '
	ELSE N''' + ' END + '
	WHEN Logins.type IN (''U'',''G'') THEN '' FROM WINDOWS ''
	WHEN Logins.type = ''C'' THEN ISNULL('' FROM CERTIFICATE '' + QUOTENAME(Cert.name),'''')
	WHEN Logins.type = ''K'' THEN ISNULL('' FROM ASYMMETRIC KEY '' + QUOTENAME(aKey.name),'''')
	ELSE '''' END +
	CASE WHEN Logins.type IN (''S'',''U'',''G'') THEN -- Note: Types, S, U and G are the only ones that have additional options.
	CASE WHEN Logins.default_database_name IS NOT NULL OR Logins.default_language_name IS NOT NULL THEN
	CASE WHEN Logins.Type = ''S'' THEN '','' ELSE '' WITH '' END
	ELSE '''' END +
	ISNULL('' DEFAULT_DATABASE = '' + QUOTENAME(Logins.default_database_name' + @Collation + N'), '''') +
	CASE WHEN Logins.default_database_name IS NOT NULL AND Logins.default_language_name IS NOT NULL THEN '','' ELSE '''' END +
	ISNULL('' DEFAULT_LANGUAGE = '' + QUOTENAME(Logins.default_language_name' + @Collation + N'), '''') +
	CASE WHEN Logins.type = ''S'' THEN
	ISNULL('', CHECK_EXPIRATION = '' + CASE WHEN sql_logins.is_expiration_checked = 1 THEN ''ON'' ELSE ''OFF'' END, '''') +
	ISNULL('', CHECK_POLICY = '' + CASE WHEN sql_logins.is_policy_checked = 1 THEN ''ON'' ELSE ''OFF'' END, '''') +
	ISNULL('', CREDENTIAL = '' + QUOTENAME(Creds.name), '''')
	ELSE '''' END
	ELSE '''' END +
	''; '' +
	CASE WHEN Logins.is_disabled = 1 THEN ''ALTER LOGIN '' + QUOTENAME(Logins.name) + '' DISABLE; '' ELSE '''' END
	AS CreateScript
	FROM sys.server_principals Logins
	LEFT OUTER JOIN sys.certificates Cert
	ON Logins.sid = Cert.sid
	LEFT OUTER JOIN sys.asymmetric_keys aKey
	ON Logins.sid = aKey.sid
	LEFT OUTER JOIN sys.sql_logins
	ON Logins.sid = sql_logins.sid
	LEFT OUTER JOIN sys.server_principal_credentials LoginCreds
	ON Logins.principal_id = LoginCreds.principal_id
	LEFT OUTER JOIN sys.credentials Creds
	ON LoginCreds.credential_id = Creds.credential_id
	WHERE 1=1 '

	IF LEN(ISNULL(@Principal,@Role)) > 0
	IF @Print = 1
	SET @sql = @sql + NCHAR(13) + N' AND Logins.name ' + @LikeOperator + N' ' + ISNULL(+QUOTENAME(@Principal,''''),QUOTENAME(@Role,''''))
	ELSE
	SET @sql = @sql + NCHAR(13) + N' AND Logins.name ' + @LikeOperator + N' ISNULL(@Principal,@Role) '

	IF LEN(@Type) > 0
	IF @Print = 1
	SET @sql = @sql + NCHAR(13) + N' AND Logins.type ' + @LikeOperator + N' ' + QUOTENAME(@Type,'''')
	ELSE
	SET @sql = @sql + NCHAR(13) + N' AND Logins.type ' + @LikeOperator + N' @Type'

	IF @DBName IS NOT NULL
	SET @sql = @sql + NCHAR(13) + N' AND Logins.SID IN (SELECT SID FROM [' + @DBName + N'].sys.database_principals
	WHERE type IN (''G'',''S'',''U'',''K'',''C''))'

	IF @IncludeMSShipped = 0
	SET @sql = @sql + NCHAR(13) + N' AND Logins.is_fixed_role = 0 ' + NCHAR(13) +
	' AND Logins.name NOT IN (''sa'',''public'') '

	IF @Print = 1
	PRINT '-- Server Principals' + NCHAR(13) + @sql + NCHAR(13) + NCHAR(13)
	ELSE
	BEGIN
	IF object_id('tempdb..##SrvPrincipals') IS NOT NULL
	DROP TABLE ##SrvPrincipals

	-- Create temp table to store the data in
	CREATE TABLE ##SrvPrincipals (
	SrvPrincipalId int NULL,
	SrvPrincipal sysname NULL,
	type char(1) NULL,
	type_desc nchar(60) NULL,
	is_disabled bit NULL,
	default_database_name sysname NULL,
	default_language_name sysname NULL,
	[check_policy] char(3) NULL,
	[check_expiration] char(3) NULL,
	Cert_or_asymmetric_key sysname NULL,
	sid varbinary(85) NULL,
	DropScript nvarchar(max) NULL,
	CreateScript nvarchar(max) NULL
	)

	SET @sql = N'INSERT INTO ##SrvPrincipals ' + NCHAR(13) + @sql

	EXEC sp_executesql @sql, N'@Principal sysname, @Role sysname, @Type varchar(30)', @Principal, @Role, @Type
	END
	--=========================================================================
	-- Server level roles
	SET @sql =
	N'SELECT Logins.principal_id AS LoginPrincipalId, Logins.name AS LoginName, Roles.name AS RoleName,
	CASE WHEN Logins.principal_id < 100 THEN NULL ELSE
	''EXEC sp_dropsrvrolemember @loginame = ''+QUOTENAME(Logins.name' + @Collation +
	','''''''')+'', @rolename = ''+QUOTENAME(Roles.name' + @Collation +
	','''''''') + '';'' END AS DropScript,
	CASE WHEN Logins.principal_id < 100 THEN NULL ELSE
	''EXEC sp_addsrvrolemember @loginame = ''+QUOTENAME(Logins.name' + @Collation +
	','''''''')+'', @rolename = ''+QUOTENAME(Roles.name' + @Collation +
	','''''''') + '';'' END AS AddScript
	FROM sys.server_role_members RoleMembers
	JOIN sys.server_principals Logins
	ON RoleMembers.member_principal_id = Logins.principal_id
	JOIN sys.server_principals Roles
	ON RoleMembers.role_principal_id = Roles.principal_id
	WHERE 1=1 '

	IF LEN(ISNULL(@Principal,'')) > 0
	IF @Print = 1
	SET @sql = @sql + NCHAR(13) + N' AND Logins.name ' + @LikeOperator + N' '+QUOTENAME(@Principal,'''')
	ELSE
	SET @sql = @sql + NCHAR(13) + N' AND Logins.name ' + @LikeOperator + N' @Principal'

	IF LEN(ISNULL(@Role,'')) > 0
	IF @Print = 1
	SET @sql = @sql + NCHAR(13) + N' AND Roles.name ' + @LikeOperator + N' '+QUOTENAME(@Role,'''')
	ELSE
	SET @sql = @sql + NCHAR(13) + N' AND Roles.name ' + @LikeOperator + N' @Role'

	IF LEN(@Type) > 0
	IF @Print = 1
	SET @sql = @sql + NCHAR(13) + N' AND Logins.type ' + @LikeOperator + N' ' + QUOTENAME(@Type,'''')
	ELSE
	SET @sql = @sql + NCHAR(13) + N' AND Logins.type ' + @LikeOperator + N' @Type'

	IF @DBName IS NOT NULL
	SET @sql = @sql + NCHAR(13) + N' AND Logins.SID IN (SELECT SID FROM [' + @DBName + N'].sys.database_principals
	WHERE type IN (''G'',''S'',''U'',''K'',''C''))'

	IF @IncludeMSShipped = 0
	SET @sql = @sql + NCHAR(13) + N' AND Logins.is_fixed_role = 0 ' + NCHAR(13) +
	' AND Logins.name NOT IN (''sa'',''public'') '

	IF @Print = 1
	PRINT '-- Server Role Members' + NCHAR(13) + @sql + NCHAR(13) + NCHAR(13)
	ELSE
	BEGIN
	IF object_id('tempdb..##SrvRoles') IS NOT NULL
	DROP TABLE ##SrvRoles

	-- Create temp table to store the data in
	CREATE TABLE ##SrvRoles (
	LoginPrincipalId int NULL,
	LoginName sysname NULL,
	RoleName sysname NULL,
	DropScript nvarchar(max) NULL,
	AddScript nvarchar(max) NULL
	)

	SET @sql = 'INSERT INTO ##SrvRoles ' + NCHAR(13) + @sql

	EXEC sp_executesql @sql, N'@Principal sysname, @Role sysname, @Type nvarchar(30)', @Principal, @Role, @Type
	END

	--=========================================================================
	-- Server Permissions
	SET @sql =
	N'SELECT Grantee.principal_id AS GranteePrincipalId, Grantee.name AS GranteeName,
	Grantor.name AS GrantorName, Permission.class_desc, Permission.permission_name,
	Permission.state_desc,
	CASE WHEN Grantee.principal_id < 100 THEN NULL ELSE
	''REVOKE '' +
	CASE WHEN Permission.class_desc = ''ENDPOINT'' THEN NULL
	WHEN Permission.[state] = ''W'' THEN ''GRANT OPTION FOR '' ELSE '''' END +
	'' '' + Permission.permission_name' + @Collation + ' +
	'' FROM '' + QUOTENAME(Grantee.name' + @Collation + ') + ''; '' END AS RevokeScript,
	CASE WHEN Grantee.principal_id < 100 THEN NULL ELSE
	CASE WHEN Permission.class_desc = ''ENDPOINT'' THEN NULL
	WHEN Permission.[state] = ''W'' THEN ''GRANT'' ELSE Permission.state_desc' + @Collation +
	' END +
	'' '' + Permission.permission_name' + @Collation + ' +
	'' TO '' + QUOTENAME(Grantee.name' + @Collation + ') + '' '' +
	CASE WHEN Permission.[state] = ''W'' THEN '' WITH GRANT OPTION '' ELSE '''' END +
	'' AS ''+ QUOTENAME(Grantor.name' + @Collation + ') + '';'' END AS GrantScript
	FROM sys.server_permissions Permission
	JOIN sys.server_principals Grantee
	ON Permission.grantee_principal_id = Grantee.principal_id
	JOIN sys.server_principals Grantor
	ON Permission.grantor_principal_id = Grantor.principal_id
	WHERE 1=1 '

	IF LEN(ISNULL(@Principal,@Role)) > 0
	IF @Print = 1
	SET @sql = @sql + NCHAR(13) + N' AND Grantee.name ' + @LikeOperator + N' ' + ISNULL(+QUOTENAME(@Principal,''''),QUOTENAME(@Role,''''))
	ELSE
	SET @sql = @sql + NCHAR(13) + N' AND Grantee.name ' + @LikeOperator + N' ISNULL(@Principal,@Role) '

	IF LEN(@Type) > 0
	IF @Print = 1
	SET @sql = @sql + NCHAR(13) + N' AND Grantee.type ' + @LikeOperator + N' ' + QUOTENAME(@Type,'''')
	ELSE
	SET @sql = @sql + NCHAR(13) + N' AND Grantee.type ' + @LikeOperator + N' @Type'

	IF @DBName IS NOT NULL
	SET @sql = @sql + NCHAR(13) + N' AND Grantee.SID IN (SELECT SID FROM [' + @DBName + N'].sys.database_principals
	WHERE type IN (''G'',''S'',''U'',''K'',''C''))'

	IF @IncludeMSShipped = 0
	SET @sql = @sql + NCHAR(13) + N' AND Grantee.is_fixed_role = 0 ' + NCHAR(13) +
	' AND Grantee.name NOT IN (''sa'',''public'') '

	IF @Print = 1
	PRINT '-- Server Permissions' + NCHAR(13) + @sql + NCHAR(13) + NCHAR(13)
	ELSE
	BEGIN
	IF object_id('tempdb..##SrvPermissions') IS NOT NULL
	DROP TABLE ##SrvPermissions

	-- Create temp table to store the data in
	CREATE TABLE ##SrvPermissions (
	GranteePrincipalId int NULL,
	GranteeName sysname NULL,
	GrantorName sysname NULL,
	class_desc nvarchar(60) NULL,
	permission_name nvarchar(128) NULL,
	state_desc nvarchar(60) NULL,
	RevokeScript nvarchar(max) NULL,
	GrantScript nvarchar(max) NULL
	)

	-- Add insert statement to @sql
	SET @sql = N'INSERT INTO ##SrvPermissions ' + NCHAR(13) + @sql

	EXEC sp_executesql @sql, N'@Principal sysname, @Role sysname, @Type nvarchar(30)', @Principal, @Role, @Type
	END

	IF @Print <> 1
	BEGIN

	IF @Output = 'None'
	PRINT ''
	ELSE IF @Output = 'CreateOnly'
	BEGIN
	SELECT CreateScript FROM ##SrvPrincipals WHERE CreateScript IS NOT NULL
	UNION ALL
	SELECT AddScript FROM ##SrvRoles WHERE AddScript IS NOT NULL
	UNION ALL
	SELECT GrantScript FROM ##SrvPermissions WHERE GrantScript IS NOT NULL
	END
	ELSE IF @Output = 'DropOnly'
	BEGIN
	SELECT DropScript FROM ##SrvPrincipals WHERE DropScript IS NOT NULL
	UNION ALL
	SELECT DropScript FROM ##SrvRoles WHERE DropScript IS NOT NULL
	UNION ALL
	SELECT RevokeScript FROM ##SrvPermissions WHERE RevokeScript IS NOT NULL
	END
	ELSE IF @Output = 'ScriptOnly'
	BEGIN
	SELECT DropScript, CreateScript FROM ##SrvPrincipals WHERE DropScript IS NOT NULL OR CreateScript IS NOT NULL
	UNION ALL
	SELECT DropScript, AddScript FROM ##SrvRoles WHERE DropScript IS NOT NULL OR AddScript IS NOT NULL
	UNION ALL
	SELECT RevokeScript, GrantScript FROM ##SrvPermissions WHERE RevokeScript IS NOT NULL OR GrantScript IS NOT NULL
	END
	ELSE IF @Output = 'Report'
	BEGIN
	SELECT SrvPrincipal, type, type_desc, is_disabled,
	STUFF((SELECT ', ' + ##SrvRoles.RoleName
	FROM ##SrvRoles
	WHERE ##SrvPrincipals.SrvPrincipalId = ##SrvRoles.LoginPrincipalId
	ORDER BY ##SrvRoles.RoleName
	FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)')
	, 1, 2, '') AS RoleMembership,
	STUFF((SELECT ', ' + ##SrvPermissions.state_desc + ' ' + ##SrvPermissions.permission_name + ' ' +
	CASE WHEN class_desc <> 'SERVER' THEN class_desc ELSE '' END
	FROM (SELECT DISTINCT * FROM ##SrvPermissions) ##SrvPermissions
	WHERE ##SrvPrincipals.SrvPrincipalId = ##SrvPermissions.GranteePrincipalId
	ORDER BY ##SrvPermissions.state_desc, ##SrvPermissions.permission_name
	FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)')
	, 1, 2, '') AS DirectPermissions
	FROM ##SrvPrincipals
	ORDER BY SrvPrincipal
	END
	ELSE -- 'Default' or no match
	BEGIN
	SELECT SrvPrincipal, type, type_desc, is_disabled, default_database_name,
	default_language_name, [check_policy], [check_expiration], Cert_or_asymmetric_key,
	sid, DropScript, CreateScript
	FROM ##SrvPrincipals ORDER BY SrvPrincipal
	IF LEN(@Role) > 0
	SELECT LoginName, RoleName, DropScript, AddScript FROM ##SrvRoles ORDER BY RoleName, LoginName
	ELSE
	SELECT LoginName, RoleName, DropScript, AddScript FROM ##SrvRoles ORDER BY LoginName, RoleName
	SELECT GranteeName, GrantorName, class_desc, permission_name, state_desc, RevokeScript, GrantScript
	FROM ##SrvPermissions ORDER BY GranteeName
	END

	IF @DropTempTables = 1
	BEGIN
	DROP TABLE ##SrvPrincipals
	DROP TABLE ##SrvRoles
	DROP TABLE ##SrvPermissions
	END
	END
	GO

	EXEC #sp_SrvPermissions

The simplest possible alternative to sp_MSforeachdb

2021-02-17T16:22:29Z

simplest_alternative_to_sp_MSforeachdb.sql

	/*

	Author: Eitan Blumin \| https://eitanblumin.com \| https://madeiradata.com
	Description:
	This is the simplest possible alternative to sp_MSforeachdb which is not too great.
	Instructions:
	1. Replace the contents of the @Command variable with the command you want to run INSIDE each database.
	2. Replace the contents of the @Parameters variable with the parameters you want the command to receive.
	3. Add parameters as needed, given @p1 as an example.
	4. Change the database filter predicates in the cursor declaration, as needed.

	Remarks:
	- The command will be run within the context of each online database in the SQL Server instance.
	- This version does NOT support the "?" replacer character.
	- Instead, you can use DB_NAME() to get the name of the current database context.

	*/

	SET NOCOUNT, XACT_ABORT, ARITHABORT ON;

	DECLARE @Command nvarchar(max) = N'PRINT DB_NAME() + N'': '' + @p1'
	DECLARE @Parameters nvarchar(max) = N'@p1 nvarchar(100)'
	DECLARE @p1 nvarchar(100) = N'I am @p1'

	DECLARE @CurrDB sysname, @spExecuteSQL NVARCHAR(1000)

	DECLARE DBs CURSOR
	LOCAL FAST_FORWARD
	FOR
	SELECT [name]
	FROM sys.databases WITH (NOLOCK)
	WHERE state = 0 /* online only */
	AND HAS_DBACCESS([name]) = 1 /* accessible only */
	AND database_id > 4 AND is_distributor = 0 /* ignore system databases */
	AND DATABASEPROPERTYEX([name], 'Updateability') = 'READ_WRITE' /* writeable only */

	OPEN DBs

	WHILE 1=1
	BEGIN
	FETCH NEXT FROM DBs INTO @CurrDB;
	IF @@FETCH_STATUS <> 0 BREAK;

	SET @spExecuteSQL = QUOTENAME(@CurrDB) + N'..sp_executesql'

	EXEC @spExecuteSQL @Command, @Parameters, @p1 /* add or remove parameters here as needed */
	WITH RECOMPILE; -- use RECOMPILE to avoid storing in plan cache for each DB
	END

	CLOSE DBs;
	DEALLOCATE DBs;

Script to configure load balancer listener IP and name for a domain-independent Availability Group in a Windows Workgroup

2021-01-20T07:22:02Z

Configure load balancer IP and name for a domain-independent AG.ps1

	<#
	.SYNOPSIS
	Script to configure load balancer listener IP and name for a domain-independent Availability Group in a Windows Workgroup

	Author: Eitan Blumin
	Date: 2021-01-20

	.DESCRIPTION
	This script is adapted from the scripts provided in the following resources:

	https://techcommunity.microsoft.com/t5/core-infrastructure-and-security/sql-server-workgroup-cluster-fcm-errors/ba-p/371387

	https://docs.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/availability-group-load-balancer-portal-configure#configure-the-cluster-to-use-the-load-balancer-ip-address

	.LINK
	See Microsoft Docs issue #69173: https://github.com/MicrosoftDocs/azure-docs/issues/69173
	#>
	$ListenerName = "<MyListenerName>"
	$IPResourceName = "<MyIPResourceName>" # the IP Address resource name
	$ListenerILBIP = "<n.n.n.n>" # the IP Address of the Internal Load Balancer (ILB). This is the static IP address for the load balancer you configured in the Azure portal.
	[int]$ListenerProbePort = <nnnnn>
	$ListenerSubnet = "255.255.255.255"
	$ClusterAGRole = "<MyClusterRoleName>"
	$ClusterNetworkName = "<MyClusterNetworkName>" # the cluster network name (Use Get-ClusterNetwork on Windows Server 2012 of higher to find the name)

	Import-Module FailoverClusters \| Out-Null

	Add-ClusterResource -Name $IPResourceName -ResourceType "IP Address" -Group $ClusterAGRole
	Get-ClusterResource -Name $IPResourceName \| Set-ClusterParameter -Multiple @{"Address"="$ListenerILBIP";"ProbePort"=$ListenerProbePort;"SubnetMask"="$ListenerSubnet";"Network"="$ClusterNetworkName";"EnableDhcp"=0}

	Add-ClusterResource -Name $ListenerName -Group $ClusterAGRole -ResourceType "Network Name"
	Get-ClusterResource -Name $ListenerName \| Set-ClusterParameter -Multiple @{"DnsName" = "$ListenerName";"RegisterAllProvidersIP" = 1}
	Set-ClusterResourceDependency -Resource $ListenerName -Dependency "[$IPResourceName]"
	Start-ClusterResource -Name $ListenerName -Verbose

	Stop-ClusterResource -Name $ClusterAGRole -Verbose
	Set-ClusterResourceDependency -Resource $ClusterAGRole -Dependency "[$ListenerName]"

	Start-ClusterResource -Name $ClusterAGRole -Verbose

Relocate AG Database Files.ps1

2021-01-14T14:38:39Z

Relocate AG Database Files.ps1

	############## Setup ##############

	$AGName = ''
	$AGPrimary = ''
	$AGSecondary = ''

	# This allows you to process just a subset of databases using the name (wildcards are possible)
	$DBNamePattern = 'AdventureW*'

	$TargetFolder = 'K:\Data\'
	$DBDataFolder = 'D:\Data\'
	$DBLogFolder = 'L:\LOG\'
	$Sourcefolder = 'L:\LOG\'
	$LogFilePattern = $DBNamePattern + '.ldf'
	$DataFilePattern = $DBNamePattern + '.mdf'

	# Yes, this is in a console and doing console output stuff..... if this is made into a "real" function this would be removed
	Clear-Host

	############## Work actually begins here ##############

	# We build a list of databases using the folder location, because we only want to move databases that are actually on the old storage. The filenames are also the names of the databases.
	$dbs = (Get-ChildItem -Path $SourceFolder* -Include $LogFilePattern ).BaseName

	# Tons of colourful output, because I like it when it runs for hours and I see something happening
	Write-Host "Beginning move of files for '$DBNamePattern' databases on $AGSecondary ($($dbs.Count) databases) " -ForegroundColor Green

	# Make sure the TargetFolder is there (using robocopy to copy folder permissions, becuase I don't know a concise way to do this in PoSh)
	if (!(Test-Path -PathType Container -Path $TargetFolder)) {

	Write-Host "$Targetfolder missing, creating folder with correct permissions" -ForegroundColor Yellow
	robocopy "$DBDataFolder" "$TargetFolder" /e /copyall /xf *
	}

	# Make sure AGSecondary can be read from (normally turned off, but we need to read from it for the move)
	Write-Host "Turning on readable secondary for AG '$AGName'" -ForegroundColor Blue
	Set-DbaAgReplica -SqlInstance $AGPrimary -AvailabilityGroup $AGname -ConnectionModeInSecondaryRole Yes -Replica $AGSecondary \| Out-Null

	foreach ($DatabaseName in $dbs) {

	Write-Host "Begin Database File Relocation for $DatabaseName in AG '$AGName'" -ForegroundColor Blue

	# Read the locations of the Data and Log files on the secondary to see if we have to move anything
	$DatabaseFiles = Get-DbaDbFile -SqlInstance $AGSecondary -Database $DatabaseName
	$DatabaseLogFile = Get-DbaDbFile -SqlInstance $AGSecondary -Database $DatabaseName \| Where-Object TypeDescription -EQ "LOG"
	$SourceFolder = $DatabaseLogFile.PhysicalName.Substring(0, ($DatabaseLogFile.PhysicalName.LastIndexOf('\') + 1))

	# Do we have work to do?
	if ($SourceFolder -ne $TargetFolder) {

	# Pause the AG for this DB
	Write-Host "Pausing data movement in AG '$AGName' for $DatabaseName" -ForegroundColor Blue
	Suspend-DbaAgDbDataMovement -SqlInstance $AGSecondary -AvailabilityGroup $AGName -Database $DatabaseName -Confirm:$false \| Out-Null

	# Remove the DB from the AG on the secondary so we can modify file paths
	# I know, "SET HADR OFF" scared me too, but it doesn't break the AG, I promise!
	# This might be replaced by Remove-DbaAgReplica, but not sure about that
	Write-Host "Removing database $DatabaseName from AG '$AGName'" -ForegroundColor Blue
	Invoke-DbaQuery -SqlInstance $AGSecondary -Database master -Query "ALTER DATABASE [$DatabaseName] SET HADR OFF;"

	# Update the metadata for the database file locations
	Write-Host "Updating Database Metadata for $DatabaseName" -ForegroundColor Blue
	foreach ($DatabaseFile in $DatabaseFiles) {
	$OldLocation = $DatabaseFile.PhysicalName

	$NewLocation = (Join-Path $TargetFolder ($DatabaseFile.PhysicalName -replace '.*\\'))

	if ($OldLocation -ne $NewLocation) {
	Invoke-DbaQuery -SqlInstance $AGSecondary -Database master -Query "ALTER DATABASE [$DatabaseName] MODIFY FILE (NAME = $($DatabaseFile.LogicalName), FILENAME = '$NewLocation');"
	}
	}
	}
	}

	# Offline the SQL Server (to release the file locks in the filesystem)
	Write-Host "Taking $AGSecondary offline" -ForegroundColor Blue
	Stop-DbaService $AGSecondary

	# Move the data files to the new location
	$dbdatafiles = Get-ChildItem -Path $DBDataFolder* -Include $DataFilePattern
	foreach ($file in $dbdatafiles) {

	Write-Host "Moving $file" -ForegroundColor Blue

	$OldLocation = Join-Path $DBDataFolder $file.Name
	$NewLocation = Join-Path $TargetFolder $file.Name

	if ($OldLocation -ne $NewLocation) {
	Move-Item -Path $OldLocation -Destination $NewLocation
	}
	}

	# Move the log files to the new location
	$dblogfiles = Get-ChildItem -Path $DBLogFolder* -Include $LogFilePattern
	foreach ($file in $dblogfiles) {

	Write-Host "Moving $file" -ForegroundColor Blue

	$OldLocation = Join-Path $DBLogFolder $file.Name
	$NewLocation = Join-Path $TargetFolder $file.Name

	if ($OldLocation -ne $NewLocation) {
	Move-Item -Path $OldLocation -Destination $NewLocation
	}
	}

	# Start SQL Server again
	Start-DbaService $AGSecondary

	# Give SQL Server a moment to start up / recover the databases
	Start-Sleep 10

	# Add the databases back into the availability group
	foreach ($DatabaseName in $dbs) {

	Write-Host "Bringing $DatabaseName back into Availability Group $AGName" -ForegroundColor Blue
	Invoke-DbaQuery -SqlInstance $AGSecondary -Database master -Query "ALTER DATABASE [$DatabaseName] SET HADR AVAILABILITY GROUP = [$AGName];"
	}

	Write-Host "Turning off readable secondary for AG '$AGName'" -ForegroundColor Blue
	Set-DbaAgReplica -SqlInstance $AGPrimary -AvailabilityGroup $AGname -ConnectionModeInSecondaryRole No -Replica $AGSecondary \| Out-Null

	Write-Host "Completed move of database files for $DatabaseName on AG '$AGName'" -ForegroundColor Green

Script to run in the SentryOne database to get CPU utilization min/max/avg/6σ for all targets

2021-01-11T19:59:34Z

SentryOne_CPU_Utilization_Stats_for_all_Sql_Server_Targets.sql

	USE SentryOne
	GO
	DECLARE
	@SiteName NVARCHAR(1000) = NULL
	,@SqlServerName NVARCHAR(1000) = NULL
	,@End_date DATETIME = NULL
	,@Start_date DATETIME = NULL
	,@CounterID SMALLINT = 1858 -- CPU %
	,@DefaultDaysBack INT = 90

	SET @End_date = ISNULL(@End_date, CONVERT(date, GETDATE()))
	SET @Start_date = ISNULL(@Start_date, @End_date - @DefaultDaysBack)

	DECLARE
	@DeviceID smallint
	, @ConnectionID smallint
	, @StartTimestamp int = [SentryOne].[dbo].[fnConvertDateTimeToTimestamp](@Start_date)
	, @EndTimestamp int = [SentryOne].[dbo].[fnConvertDateTimeToTimestamp](@End_date)
	, @RangeSizeMinutes int = DATEDIFF(minute, @Start_date, @End_date)

	DECLARE @AggPerf AS TABLE
	(
	[AvgVal] FLOAT,
	[MinVal] FLOAT,
	[MaxVal] FLOAT,
	[STD] FLOAT
	)

	DECLARE @Result AS TABLE
	(
	[SiteName] NVARCHAR(1000),
	[SqlServerName] NVARCHAR(1000),
	[AvgVal] FLOAT,
	[MinVal] FLOAT,
	[MaxVal] FLOAT,
	[STD] FLOAT
	)

	DECLARE SqlServers CURSOR
	LOCAL FAST_FORWARD
	FOR
	SELECT d.ObjectName, s.Name
	FROM Device AS d
	INNER JOIN Site AS s ON d.SiteID = s.ID
	WHERE d.ID IN (SELECT DeviceID FROM vwSqlServer)
	AND (@SiteName IS NULL OR s.Name = @SiteName)
	AND (@SqlServerName IS NULL OR d.ObjectName = @SqlServerName)

	OPEN SqlServers

	WHILE 1=1
	BEGIN

	FETCH NEXT FROM SqlServers INTO @SqlServerName, @SiteName
	IF @@FETCH_STATUS <> 0 BREAK;

	SELECT @ConnectionID = c.ID, @DeviceID = d.ID
	FROM dbo.Device AS d
	INNER JOIN dbo.EventSourceConnection AS c
	ON d.ID = c.DeviceID
	INNER JOIN dbo.EventSourceConnectionType AS ect
	ON c.EventSourceConnectionTypeID = ect.ObjectID
	INNER JOIN dbo.[Site] AS s
	ON d.SiteID = s.ID
	WHERE
	s.Name = @SiteName
	AND ect.Name = 'SQL Server'
	AND @SqlServerName IN (d.HostName, d.ObjectName, d.FriendlyName, c.ServerName, c.ObjectName, c.FriendlyName)

	-- Processor time:
	INSERT INTO @AggPerf
	EXEC [dbo].[GetCounterBaselineDataAggregateForDeviceByTimestamp] @DeviceID, @CounterID, @StartTimestamp, @EndTimestamp, @RangeSizeMinutes

	INSERT INTO @Result
	SELECT @SiteName AS SiteName, @SqlServerName AS SqlServerName, *
	FROM @AggPerf

	DELETE @AggPerf;

	END

	CLOSE SqlServers
	DEALLOCATE SqlServers;

	SELECT
	c.CounterName
	, ag.*
	, SixSigmaMax = (SELECT MIN(Val) FROM (VALUES(ag.AvgVal + ag.STD * 6),(ag.MaxVal)) AS a(Val))
	FROM @Result AS ag
	INNER JOIN dbo.PerformanceAnalysisCounter AS c ON c.ID = @CounterID

T-SQL script to retrieve the hierarchy tree for a given table, based on foreign key references.

2021-01-07T16:07:34Z

Foreign_Key_Hierarchy_Tree.sql

	/*
	Retrieve Foreign Key Hierarchy Tree
	===================================
	Author: Eitan Blumin \| https://eitanblumin.com \| https://madeiradata.com
	Date: 2021-01-07
	Description:
	Retrieve the hierarchy tree for a given table,
	based on foreign key references.
	Use this script to map out your entity relational structure,
	see which foreign keys are dependent on a given table,
	their names, and various constraint properties.

	Arguments:
	===========
	@TableName - Table name used as the initial "point of entry". Hierarchy tree will extend from this "root" table.
	@MaxLevel - Used for avoiding exaggerated recursion depth.
	@AvoidInfiniteRecurse - Uses the hierarchyid data type to avoid infinite recursion loop.
	===================================
	*/
	DECLARE
	@TableName SYSNAME = 'dbo.Catalog',
	@MaxLevel INT = 100,
	@AvoidInfiniteRecurse BIT = 0

	SET NOCOUNT ON;
	WITH Tree
	AS
	(
	SELECT 1 AS lvl, fk.[name], fk.[object_id], fk.[parent_object_id], fk.[referenced_object_id]
	, fk.delete_referential_action, fk.update_referential_action
	, fk.is_not_trusted, fk.is_disabled, fk.is_not_for_replication
	, hid = CAST('/' + CONVERT(varchar, fk.[referenced_object_id]) + '/' + CONVERT(varchar, fk.[parent_object_id]) + '/' AS hierarchyid)
	FROM sys.foreign_keys AS fk
	WHERE fk.referenced_object_id = OBJECT_ID(@TableName)

	UNION ALL

	SELECT Tree.lvl + 1 AS lvl, fk.[name], fk.[object_id], fk.[parent_object_id], fk.[referenced_object_id]
	, fk.delete_referential_action, fk.update_referential_action
	, fk.is_not_trusted, fk.is_disabled, fk.is_not_for_replication
	, hid = CAST(Tree.hid.ToString() + CONVERT(varchar, fk.[parent_object_id]) + '/' AS hierarchyid)
	FROM sys.foreign_keys AS fk
	INNER JOIN Tree ON fk.referenced_object_id = Tree.parent_object_id
	WHERE Tree.parent_object_id <> Tree.referenced_object_id
	AND (@AvoidInfiniteRecurse = 0 OR Tree.hid.ToString() NOT LIKE '%/' + CONVERT(varchar,fk.[parent_object_id]) + '/%')
	AND (@MaxLevel IS NULL OR Tree.lvl < @MaxLevel)
	)
	SELECT
	lvl = MIN(lvl)
	, foreign_key_name = [name]
	, table_name = QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id)) + '.' + QUOTENAME(OBJECT_NAME(parent_object_id))
	, referenced_table_name = QUOTENAME(OBJECT_SCHEMA_NAME(referenced_object_id)) + '.' + QUOTENAME(OBJECT_NAME(referenced_object_id))
	, on_delete_action = delete_referential_action
	, on_update_action = update_referential_action
	, is_not_trusted
	, is_disabled
	, is_not_for_replication
	FROM Tree
	GROUP BY
	[name]
	, parent_object_id, referenced_object_id
	, delete_referential_action, update_referential_action
	, is_not_trusted, is_disabled, is_not_for_replication
	ORDER BY 1, 2, 3, 4

Generic script to delete time based data in batches

2020-12-26T17:01:29Z

Delete_Batches_From_Table_By_DateTime_Column.sql

	DECLARE
	@DatabaseName SYSNAME = 'MyDB',
	@TableName SYSNAME = 'MyTable',
	@DateTimeColumnName SYSNAME = 'MyColumn',
	@ThresholdDateTime DATETIME = DATEADD(DAY, -14, GETDATE()),
	@BatchSize INT = 10000,
	@SleepBetweenBatches VARCHAR(17) = '00:00:00.6'

	SET NOCOUNT ON;

	DECLARE @CMD NVARCHAR(MAX), @Executor NVARCHAR(1000);
	SET @DatabaseName = ISNULL(@DatabaseName, DB_NAME());

	IF DB_ID(@DatabaseName) IS NULL OR DATABASEPROPERTYEX(@DatabaseName, 'Updateability') <> 'READ_WRITE' OR DATABASEPROPERTYEX(@DatabaseName, 'Status') <> 'ONLINE'
	RAISERROR(N'Database "%s" is not found or not accessible or not writeable.', 16, 1, @DatabaseName);
	ELSE
	BEGIN
	SET @CMD = N'
	IF NOT EXISTS (SELECT NULL FROM sys.columns WHERE object_id = OBJECT_ID(@TableName) AND [name] = @ColumnName)
	RAISERROR(N''Column "%s" was not found for table "%s"!'',16,1, @ColumnName, @TableName);
	ELSE
	BEGIN
	WHILE 1=1
	BEGIN
	DELETE TOP (@BatchSize)
	FROM ' + @TableName + N'
	WHERE ' + @DateTimeColumnName + N' < @ThresholdDateTime

	IF @@ROWCOUNT = 0
	BREAK;

	IF @SleepBetweenBatches IS NOT NULL
	WAITFOR DELAY @SleepBetweenBatches;
	END
	END'

	SET @Executor = QUOTENAME(@DatabaseName) + N'..sp_executesql'

	PRINT N'Database: ' + @DatabaseName
	PRINT @CMD;

	EXEC @Executor @CMD
	, N'@TableName SYSNAME, @ColumnName SYSNAME, @BatchSize INT, @ThresholdDateTime DATETIME, @SleepBetweenBatches VARCHAR(17)'
	, @TableName, @DateTimeColumnName,@BatchSize, @ThresholdDateTime, @SleepBetweenBatches
	END

R stored procedure to load Excel files, by Matteo Lorini

2020-12-25T04:39:58Z

R_myReadXcelProc.sql

	SET ANSI_NULLS ON
	GO

	SET QUOTED_IDENTIFIER ON
	GO
	/*
	Based on sample by Matteo Lorini:
	https://www.mssqltips.com/sqlservertip/6622/stored-procedure-in-sql-server-with-r-code/

	The readxl package needs to be installed first:
	https://www.mssqltips.com/sqlservertip/4982/installing-external-r-packages-to-use-with-sql-server-2017/
	*/
	CREATE PROCEDURE [dbo].[R_myReadXcelProc]
	@parallel_outer bit = 0,
	@srcFileName_outer varchar(max),
	@xlsxRows_outer float output
	AS
	BEGIN TRY
	exec sp_execute_external_script
	@language = N'R',
	@script = N'
	myReadXcelFunc <- function (srcFileName)
	{
	library(readxl)
	myFile <- read_xlsx(srcFileName)
	numRows <- nrow(myFile)
	myFileDF <- data.frame(myFile)
	retList <- list(xlsxFile = myFileDF, xlsxRows = numRows)
	return(retList)
	}
	result <- myReadXcelFunc(srcFileName = srcFileName)
	if (is.list(result)) {
	OutputDataSet <- result$xlsxFile
	xlsxRows <- result$xlsxRows
	} else stop("the R function must return a list")
	',
	@parallel = @parallel_outer,
	@params = N'@srcFileName varchar(max), @xlsxRows float output',
	@srcFileName = @srcFileName_outer,
	@xlsxRows = @xlsxRows_outer output
	END TRY
	BEGIN CATCH
	THROW;
	END CATCH;
	GO

T-SQL script to read the contents of a SQLDump file's txt file

2020-12-08T12:38:41Z

SQLDump_File_Examine.sql

	/*
	Read contents of a SQLDump file's txt file
	=============================================
	Author: Eitan Blumin \| https://www.madeiradata.com
	Date: 2020-12-08
	*/
	SET NOCOUNT, ARITHABORT, XACT_ABORT ON;
	DECLARE @FilePath NVARCHAR(4000), @CMD NVARCHAR(MAX)

	-- Use below to read the contents of latest memory dump file (mdmp) generated by the current instance:
	SET @FilePath = (SELECT TOP 1 REPLACE([filename], N'.mdmp', N'.txt') FROM sys.dm_server_memory_dumps ORDER BY creation_time DESC)

	-- Use below to read the contents of an explicitely specified txt file:
	--SET @FilePath = N'C:\temp\SQLDump0001.txt'

	IF OBJECT_ID('tempdb..#Bulk') IS NOT NULL DROP TABLE #Bulk;
	CREATE TABLE #Bulk(Content NVARCHAR(MAX));

	SET @CMD = N'
	BULK INSERT #Bulk
	FROM ' + QUOTENAME(@FilePath, N'''')

	EXEC(@CMD);

	ALTER TABLE #Bulk ADD ID INT IDENTITY(1,1);
	DECLARE @FromID INT, @ToID INT;

	SELECT @FromID = MIN(ID), @ToID = MAX(ID)
	FROM (
	SELECT TOP 2 ID
	FROM #Bulk
	WHERE Content LIKE '* **************%'
	OR Content LIKE '*%MODULE%BASE%END%SIZE%'
	ORDER BY ID ASC
	) AS q

	SELECT Content = N'Source: ' + @FilePath
	UNION ALL
	SELECT Content
	FROM #Bulk
	WHERE Content LIKE '* %'
	AND LTRIM(Content) <> '*'
	AND ID > @FromID
	AND ID < @ToID

Template Powershell script with built-in transcript log management

2020-12-07T14:58:05Z

Powershell_Template_with_Transcript.ps1

	# when creating a scheduled task to run such scripts, use the following structure example:
	# powershell.exe -NoProfile -ExecutionPolicy Bypass -File "C:\Madeira\Powershell_Template_with_Transcript.ps1"
	Param
	(
	[string]$logFileFolderPath = "C:\Madeira\log",
	[string]$logFilePrefix = "my_ps_script_",
	[string]$logFileDateFormat = "yyyyMMdd_HHmmss",
	[int]$logFileRetentionDays = 30
	)
	Process {
	#region initialization
	function Get-TimeStamp {
	Param(
	[switch]$NoWrap,
	[switch]$Utc
	)
	$dt = Get-Date
	if ($Utc -eq $true) {
	$dt = $dt.ToUniversalTime()
	}
	$str = "{0:MM/dd/yy} {0:HH:mm:ss}" -f $dt

	if ($NoWrap -ne $true) {
	$str = "[$str]"
	}
	return $str
	}

	if ($logFileFolderPath -ne "")
	{
	if (!(Test-Path -PathType Container -Path $logFileFolderPath)) {
	Write-Output "$(Get-TimeStamp) Creating directory $logFileFolderPath" \| Out-Null
	New-Item -ItemType Directory -Force -Path $logFileFolderPath \| Out-Null
	} else {
	$DatetoDelete = $(Get-Date).AddDays(-$logFileRetentionDays)
	Get-ChildItem $logFileFolderPath \| Where-Object { $_.Name -like "$logFilePrefix" -and $_.LastWriteTime -lt $DatetoDelete } \| Remove-Item \| Out-Null
	}

	$logFilePath = $logFileFolderPath + "\$logFilePrefix" + (Get-Date -Format $logFileDateFormat) + ".LOG"

	# attempt to start the transcript log, but don't fail the script if unsuccessful:
	try
	{
	Start-Transcript -Path $logFilePath -Append
	}
	catch [Exception]
	{
	Write-Warning "$(Get-TimeStamp) Unable to start Transcript: $($_.Exception.Message)"
	$logFileFolderPath = ""
	}
	}
	#endregion initialization


	#region install-modules
	# replace the array below with any modules that your script depends on.
	# you can remove this region if your script doesn't need importing any modules.
	$modules = @("PSFramework", "PSModuleDevelopment", "dbatools")

	foreach ($module in $modules) {
	if (Get-Module -ListAvailable -Name $module) {
	Write-Verbose "$(Get-TimeStamp) $module already installed"
	}
	else {
	Write-Information "$(Get-TimeStamp) Installing $module"
	Install-Module $module -Force -SkipPublisherCheck -Scope CurrentUser -ErrorAction Stop \| Out-Null
	Import-Module $module -Force -Scope Local \| Out-Null
	}
	}
	#endregion install-modules


	#region main



	# TODO: Replace this code with your actual script body:

	Write-Output "$(Get-TimeStamp) Example output message. Check out the timestamp on this bad boy."

	# When using Invoke-Sqlcmd, be sure to add parameters -OutputSqlErrors $true -Verbose to capture all output. For example:
	Invoke-Sqlcmd -Server "." -Database "master" -Query "PRINT 'This is the output of a SQL command, generated by: ' + PROGRAM_NAME() + ' on server ' + @@SERVERNAME" -QueryTimeout 0 -OutputSqlErrors $true -Verbose



	#endregion main


	#region finalization
	if ($logFileFolderPath -ne "") { Stop-Transcript }
	#endregion finalization
	}

T-SQL script to find redundant indexes within the current database, also outputs index usage stats and drop commands

2020-12-06T17:15:12Z

Redundant_Indexes.sql

	SET NOCOUNT ON;
	DECLARE @MinimumRowsInTable INT = 200000;
	IF OBJECT_ID('tempdb..#FindOnThisDB') IS NOT NULL DROP TABLE #FindOnThisDB;
	;WITH Indexes AS
	(
	select
	sets.schema_id,
	sets.table_o_id,
	sets.key_column_list,
	sets.include_column_list,
	sets.is_unique,
	sets.index_number
	from
	(
	SELECT
	SCHEMA_DATA.schema_id,
	TABLE_DATA.object_id as table_o_id,
	INDEX_DATA.object_id as index_o_id,
	INDEX_DATA.index_id as index_number,
	INDEX_DATA.is_unique,
	INDEX_DATA.name,
	(
	SELECT QUOTENAME(cast(keyCol.column_id as varchar) + CASE WHEN keyCol.is_descending_key = 1 THEN 'd' ELSE 'a' END, '{')
	FROM sys.tables AS T
	INNER JOIN sys.indexes idx ON T.object_id = idx.object_id
	INNER JOIN sys.index_columns keyCol ON idx.object_id = keyCol.object_id AND idx.index_id = keyCol.index_id
	WHERE INDEX_DATA.object_id = idx.object_id
	AND INDEX_DATA.index_id = idx.index_id
	AND keyCol.is_included_column = 0
	ORDER BY keyCol.key_ordinal
	FOR XML PATH('')
	) AS key_column_list ,
	(
	SELECT QUOTENAME(cast(keyColINC.column_id as varchar), '{')
	FROM sys.tables AS T
	INNER JOIN sys.indexes idxINC ON T.object_id = idxINC.object_id
	INNER JOIN sys.index_columns keyColINC ON idxINC.object_id = keyColINC.object_id AND idxINC.index_id = keyColINC.index_id
	WHERE
	INDEX_DATA.object_id = idxINC.object_id
	AND INDEX_DATA.index_id = idxINC.index_id
	AND keyColINC.is_included_column = 1
	ORDER BY keyColINC.column_id
	FOR XML PATH('')
	) AS include_column_list
	FROM sys.indexes INDEX_DATA
	INNER JOIN sys.tables TABLE_DATA ON TABLE_DATA.object_id = INDEX_DATA.object_id
	INNER JOIN sys.schemas SCHEMA_DATA ON SCHEMA_DATA.schema_id = TABLE_DATA.schema_id

	WHERE TABLE_DATA.is_ms_shipped = 0
	and INDEX_DATA.is_disabled = 0
	AND INDEX_DATA.data_space_id > 0
	) AS sets
	LEFT JOIN sys.partitions p
	ON sets.table_o_id = p.OBJECT_ID AND sets.index_number = p.index_id
	where key_column_list is not null
	GROUP BY sets.schema_id, sets.table_o_id, sets.index_number, sets.is_unique, sets.key_column_list, sets.include_column_list
	HAVING sum(p.rows) >= @MinimumRowsInTable
	)
	SELECT
	DISTINCT
	DUPE1.schema_id as schema_id,
	DUPE1.table_o_id as table_object_id,
	DUPE1.index_number as redundant_index_id ,
	DUPE2.index_number as containing_index_id ,
	DUPE1.key_column_list, DUPE1.include_column_list
	into #FindOnThisDB
	FROM Indexes DUPE1
	INNER JOIN Indexes DUPE2
	ON
	DUPE1.schema_id = DUPE2.schema_id
	AND DUPE1.table_o_id = DUPE2.table_o_id
	AND DUPE1.index_number <> 1 -- do not consider clustered indexes as redundant
	AND DUPE1.is_unique = 0 -- do not consider unique indexes as redundant
	AND
	(
	DUPE1.key_column_list = LEFT(DUPE2.key_column_list, LEN(DUPE1.key_column_list))
	and
	(
	DUPE1.include_column_list is null
	or
	DUPE1.include_column_list = LEFT(DUPE2.include_column_list, LEN(DUPE1.include_column_list))
	)
	)
	AND DUPE1.index_number <> DUPE2.index_number
	;

	SELECT
	database_name = DB_NAME(),
	--tb.object_id,
	--redundant_index_id = ind1.index_id,
	--containing_index_id = ind2.index_id,
	schema_name = sch.name,
	table_name = tb.name,
	ind1.name as redundant_index_name,
	redundant_key_columns = STUFF
	((
	SELECT ', ' + QUOTENAME(col.name) + CASE WHEN keyCol.is_descending_key = 1 THEN ' DESC' ELSE ' ASC' END
	FROM sys.index_columns keyCol
	inner join sys.columns col on keyCol.object_id = col.object_id AND keyCol.column_id = col.column_id
	WHERE ind1.object_id = keyCol.object_id
	AND ind1.index_id = keyCol.index_id
	AND keyCol.is_included_column = 0
	ORDER BY keyCol.key_ordinal
	FOR XML PATH('')), 1, 2, ''),
	redundant_include_columns = STUFF
	((
	SELECT ', ' + QUOTENAME(col.name)
	FROM sys.index_columns keyCol
	inner join sys.columns col on keyCol.object_id = col.object_id AND keyCol.column_id = col.column_id
	WHERE ind1.object_id = keyCol.object_id
	AND ind1.index_id = keyCol.index_id
	AND keyCol.is_included_column = 1
	ORDER BY keyCol.key_ordinal
	FOR XML PATH('')), 1, 2, ''),
	redundant_index_seeks = us1.user_seeks,
	redundant_index_scans = us1.user_scans,
	redundant_index_updates = us1.user_updates,
	containing_index_name = ind2.name,
	containing_key_columns = STUFF
	((
	SELECT ', ' + QUOTENAME(col.name) + CASE WHEN keyCol.is_descending_key = 1 THEN ' DESC' ELSE ' ASC' END
	FROM sys.index_columns keyCol
	inner join sys.columns col on keyCol.object_id = col.object_id AND keyCol.column_id = col.column_id
	WHERE ind2.object_id = keyCol.object_id
	AND ind2.index_id = keyCol.index_id
	AND keyCol.is_included_column = 0
	ORDER BY keyCol.key_ordinal
	FOR XML PATH('')), 1, 2, ''),
	containing_include_columns = STUFF
	((
	SELECT ', ' + QUOTENAME(col.name)
	FROM sys.index_columns keyCol
	inner join sys.columns col on keyCol.object_id = col.object_id AND keyCol.column_id = col.column_id
	WHERE ind2.object_id = keyCol.object_id
	AND ind2.index_id = keyCol.index_id
	AND keyCol.is_included_column = 1
	ORDER BY keyCol.key_ordinal
	FOR XML PATH('')), 1, 2, ''),
	containing_index_seeks = us2.user_seeks,
	containing_index_scans = us2.user_scans,
	containing_index_updates = us2.user_updates,
	DropCmd = N'USE ' + QUOTENAME(DB_NAME()) + N'; DROP INDEX ' + QUOTENAME(ind1.name) + N' ON ' + QUOTENAME(sch.name) + N'.' + QUOTENAME(tb.name) + N';'
	from #FindOnThisDB AS tbl
	INNER JOIN sys.tables tb
	ON tb.object_id = tbl.table_object_id
	INNER JOIN sys.schemas sch
	ON sch.schema_id = tbl.schema_id
	INNER JOIN sys.indexes ind1
	ON ind1.object_id = tbl.table_object_id and ind1.index_id = tbl.redundant_index_id
	INNER JOIN sys.indexes ind2
	ON ind2.object_id = tbl.table_object_id and ind2.index_id = tbl.containing_index_id
	LEFT JOIN sys.dm_db_index_usage_stats AS us1 ON us1.database_id = DB_ID() AND us1.object_id = ind1.object_id AND us1.index_id = ind1.index_id
	LEFT JOIN sys.dm_db_index_usage_stats AS us2 ON us2.database_id = DB_ID() AND us2.object_id = ind2.object_id AND us2.index_id = ind2.index_id

	--DROP TABLE #FindOnThisDB

TSQL script to check for invalid owners of system Roles, Schemas, or Databases

2020-11-25T08:27:11Z

Invalid_owner_for_system_schema_role_or_database.sql

	/*
	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.
	All system schemas such as sys, dbo, db_owner, db_datawriter, db_datareader, etc. must be owned by the system role or user of the same name.

	It's a 3-part relationship like so:
	schema X - owned by role X - owned by dbo.

	If the database is a system database, its owner should be sa (or equivalent, if it was renamed).

	Invalid owners for such system objects can potentially cause severe errors during version updates/upgrades, or when using certain HADR features.

	This script will detect any such misconfigurations, and provide you with the proper remediation scripts to fix it.
	*/
	SET NOCOUNT, ARITHABORT, XACT_ABORT ON;
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
	DECLARE @CMD NVARCHAR(MAX), @DBName SYSNAME, @Executor NVARCHAR(1000), @SaName SYSNAME

	SELECT @SaName = [name] FROM sys.server_principals WHERE sid = 0x01;

	SET @CMD = N'SELECT DB_ID(), DB_NAME(), ''SCHEMA'', sch.[name], pr.[name]
	FROM sys.schemas AS sch
	LEFT JOIN sys.database_principals AS pr ON sch.principal_id = pr.principal_id
	WHERE (sch.schema_id >= 16384 OR DB_NAME() = ''msdb'')
	AND (pr.principal_id IS NULL
	OR (sch.[name] NOT IN (''managed_backup'',''smart_admin'',''MS_PerfDashboard'') AND sch.[name] <> pr.[name])
	OR (sch.[name] IN (''managed_backup'',''smart_admin'',''MS_PerfDashboard'') AND sch.principal_id <> 1)
	)

	UNION ALL

	SELECT DB_ID(), DB_NAME(), ''ROLE'', rol.[name], pr.[name]
	FROM sys.database_principals AS rol
	LEFT JOIN sys.database_principals AS pr ON rol.owning_principal_id = pr.principal_id
	WHERE (rol.principal_id >= 16384 OR DB_NAME() = ''msdb'')
	AND rol.type = ''R''
	AND (pr.principal_id IS NULL OR rol.owning_principal_id <> 1)

	UNION ALL

	SELECT DB_ID(), DB_NAME(), ''DATABASE'', DB_NAME(), sp.[name] COLLATE database_default
	FROM sys.database_principals AS dp
	LEFT JOIN sys.server_principals AS sp ON dp.sid = sp.sid
	WHERE dp.principal_id = 1
	AND DB_ID() <= 4
	AND (sp.sid IS NULL OR sp.sid <> 0x01)'

	DECLARE @Result AS TABLE
	(
	[DBId] INT NULL, DBName SYSNAME NULL, ObjType SYSNAME NULL, SchemaName SYSNAME NULL, RoleName SYSNAME NULL
	, DefaultOwner AS (CASE WHEN ObjType = 'SCHEMA' THEN SchemaName WHEN ObjType = 'ROLE' THEN 'dbo' END)
	);

	DECLARE DBs CURSOR
	LOCAL FAST_FORWARD
	FOR
	SELECT [name]
	FROM sys.databases
	WHERE state = 0 AND is_read_only = 0
	AND DATABASEPROPERTYEX([name], 'Updateability') = 'READ_WRITE'

	OPEN DBs
	FETCH NEXT FROM DBs INTO @DBName

	WHILE @@FETCH_STATUS = 0
	BEGIN
	SET @Executor = QUOTENAME(@DBName) + N'..sp_executesql'

	INSERT INTO @Result
	EXEC @Executor @CMD

	FETCH NEXT FROM DBs INTO @DBName
	END

	CLOSE DBs
	DEALLOCATE DBs

	SELECT
	Msg = N'In server: ' + @@SERVERNAME + N', database: ' + QUOTENAME(DBName)
	+ N', system ' + ObjType + N'::' + QUOTENAME(SchemaName) + N' has an invalid owner ' + ISNULL(QUOTENAME(RoleName), N'(null)')
	+ N'. should be: ' + QUOTENAME(ISNULL(DefaultOwner, @SaName))
	, RemediationCmd = N'USE ' + QUOTENAME(DBName) + N'; ALTER AUTHORIZATION ON ' + UPPER(ObjType) + N'::' + QUOTENAME(SchemaName) + N' TO ' + QUOTENAME(ISNULL(DefaultOwner, @SaName)) + N';'
	FROM @Result
	ORDER BY [DBId] ASC