ghotz

with cte as
(
SELECT 
    sp.name AS login_name,
    sp.default_database_name,
    CASE 
        WHEN sp.default_database_name IS NULL THEN 'NO_DEFAULT_SET'
        WHEN d.name IS NULL THEN 'DB_MISSING'
        ELSE d.state_desc
    END AS db_status

ghotz

USE SSISDB;
GO

DECLARE @SearchValue NVARCHAR(250) = REPLACE('%application%', '_', '[_]');

-- A. Search inside Project and Package Parameters (Fixed Column Name)
SELECT 
    f.name AS [Folder Name],
    p.name AS [Project Name],

ghotz

--
-- Get deadlocks details from an Azure SQL Database XE Session with as ringbuffer as destination
--
IF OBJECT_ID('tempdb..#deadlocks') IS NOT NULL
	DROP TABLE #deadlocks;

WITH   XmlDataSet AS
(
       SELECT CAST(xet.target_data  AS xml) AS XMLDATA,*
       FROM   sys.dm_xe_database_session_targets AS xet

ghotz

SELECT
	[name] AS database_name, is_auto_shrink_on
,	'ALTER DATABASE [' + [name] + ']' +	' SET AUTO_SHRINK OFF;' AS alter_command
FROM	sys.databases
WHERE	[name] NOT IN ('master','model','msdb','tempdb')
  AND	state_desc = 'ONLINE'
  AND	is_auto_shrink_on = 1
ORDER BY [name]

ghotz

$instance = 'localhost'
$logins   = 'group1','group2'

foreach ($login in $logins)
{
    Write-Output "Processing $login"
    #$file     = "C:\temp\backup-$($login.Replace('\','_')).sql"
    #Export-DbaLogin -SqlInstance $instance -Login $login -FilePath $file -ObjectLevel
    Remove-DbaDbUser -SqlInstance $instance -User $login -Force
    Remove-DbaLogin -SqlInstance $instance -Login $login -Force -Confirm:$false

ghotz

DECLARE	@StartSearchDate	datetime;
DECLARE	@SecondsAround		int;
DECLARE	@Databases			TABLE (DatabaseName sysname NOT NULL PRIMARY KEY);

SET	@StartSearchDate = GETDATE() - 2;
SET	@SecondsAround = 1;

--
-- Get error logs
--

ghotz

DECLARE	@original_path		sysname = N'E:\SQLServer\MSSQL15.SQL2022\MSSQL\DATA';
DECLARE	@destination_path	sysname = N'E:\SQLServer\MSSQL16.SQL2022\MSSQL\DATA';


DECLARE	@sqlcmd nvarchar(max) = (
SELECT
		N'ALTER DATABASE [tempdb]'
	+	N' MODIFY FILE (NAME = [' + [name] + ']'
	+	N', FILENAME = ''' + REPLACE(physical_name, @original_path, @destination_path) + ''');'
	+	CHAR(10)

ghotz

USE [msdb]
GO

/****** Object:  Alert [AG Connection Timeout]    Script Date: 1/13/2023 10:36:31 AM ******/
EXEC msdb.dbo.sp_add_alert @name=N'AG Connection Timeout', 
		@message_id=35206, 
		@severity=0, 
		@enabled=1, 
		@delay_between_responses=60, 
		@include_event_description_in=1, 

ghotz

SELECT DISTINCT
	RP.[name]
,	RP.[type_desc] AS role_type
,	PM.class_desc AS permission_type
,	PM.[permission_name]
,	pm.state_desc
,	CASE 
	WHEN O1.type_desc IS NULL OR O1.type_desc = 'SYSTEM_TABLE'
	THEN PM.class_desc
	ELSE O1.[type_desc]

ghotz

/*Note This script creates two stored procedures in the master database. The procedures are named sp_hexadecimal and sp_help_revlogin. */
 -- Create stored procedure sp_hexadecimal
USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
  DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
    @binvalue varbinary(256),
    @hexvalue varchar (514) OUTPUT