Monthly Archives: September 2020

Extended Event – Capture Permission Denied

Create a extended event to capture permission denied messages

CREATE EVENT SESSION [PermissionDenied] ON SERVER 
ADD EVENT sqlserver.error_reported(
    ACTION(sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.database_name,sqlserver.nt_username,sqlserver.sql_text,sqlserver.username)
    WHERE ([sqlserver].[like_i_sql_unicode_string]([message],N'%permission%') OR [sqlserver].[like_i_sql_unicode_string]([message],N'%denied%')))
ADD TARGET package0.event_file(SET filename=N'PermissionDenied',max_file_size=(250),max_rollover_files=(2))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=ON)
GO
ALTER EVENT SESSION [PermissionDenied] ON SERVER STATE = START;   -- STOP;
GO

List all triggers

create table ##triggers (DbName sysname, TriggerName sysname);
insert into ##triggers(DbName, TriggerName)
exec sp_msforeachdb 'use [?]; SELECT  ''?'' as DbName, name FROM sys.triggers WHERE type = ''TR'';';
select * from ##triggers;
drop table ##triggers;

SSIS Package Execution History

Pivot SSIS execution history durations

use SSISDB
go
set nocount on;
DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);
SET @columns = N'';
SELECT @columns += N', p.' + QUOTENAME(StartDate)
FROM (select distinct cast(start_time as date) as StartDate from catalog.executions) AS x;

SET @sql = N'SELECT FolderName, PackageName, ' + STUFF(@columns, 1, 2, '') + '
FROM
(
    select
    package_name as PackageName,
    ce.folder_name as FolderName,
    cast(start_time as date) as StartDate, 
    datediff(s,start_time,end_time) as DurationSec
   FROM catalog.executions as ce
) AS j
PIVOT
(
  SUM(DurationSec) FOR StartDate IN ('
  + STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '')
  + ')
) AS p;';
PRINT @sql;
EXEC sp_executesql @sql;