SQL Spark

  USE master Declare @strFind varchar(max) Set @strFind = '%ma_cancadeal%' select distinct object_name(OBJECT_ID)  from sys.sql_modules  where definition like @strFind  order by object_name(OBJECT_ID)  declare @sql varchar(max) SELECT @sql = 'USE ? print ''?''; select ''?'' as DB_Name, object_Name(object_id),* from sys.sql_modules WHERE definition LIKE ''%'+@strFind+'%'''  EXEC sp_MSforeachdb @sql 

  -- Save File as .xdl and then open in SSMS  SELECT DeadloackDetail = CONVERT(xml, event_data).query('/event/data/value/child::*'), CONVERT(xml, event_data).value('(event[@name="xml_deadlock_report"]/@timestamp)[1]','datetime') as Execution_Time FROM sys.fn_xe_file_target_read_file('F:\SQL13BIN\MSSQL13.MSSQLSERVER\MSSQL\Log\system_health*.xel', null, null, null) WHERE object_name like 'xml_deadlock_report'

  1. Register SPN for SQL service account SetSPN -S MSSQLSvc/win2012r2-e424b.dynamic.sit.cloud.td.com:3341 D2-TDBFG\TCRBI084SQLS 2. Enable Delegate in Active Directory, go to SQL server account Properties, on "Delegation" tab, select "Trust this user for delegation to any service (Kerberos only)" 3. Verify SPN and delegate Get-ADUser -Identity TCRDM463SQLSS -Properties * After Enabled 4. confirm Authentication use K erberos select ServerName =@@ServerName, Servicename, service_account, status_desc from sys.dm_server_services select @@ServerName, auth_scheme from sys.dm_exec_connections where session_id =@@SPID 5. Create Linked Server use AD authentication, access based on account use linked server USE [master] GO /****** Object:  LinkedServer [DEV3]    Script Date: 11/9/2021 12:12:02 PM ******/ IF NOT EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'DEV3') BEGIN EXEC master.dbo.sp_addlinkedserver @server = N'DEV3...