Monthly Archives: October 2016

SSISCatalogReaderRole

I needed to give a group of business intelligence specialists read permsisions on the SSIS Catalog. I created this scritp to achieve this goal. This script greats a database role with the required permissions. At last I mapped a Windows Group to this database role.

USE SSISDB
GO
create role SsisCatalogReadRole authorization dbo

-- declare variables
declare @object_type_folder smallint = 1
declare @object_type_project smallint = 2
declare @object_id bigint
declare @principal_id int = (select principal_id from sys.database_principals as dp where dp.name =  'SsisCatalogReadRole')
declare @permission_type_read smallint = 1
declare @permission_type_read_objects smallint = 101

-- grand read and read_object permisions for each folder
DECLARE @folder_id bigint

DECLARE grant_folder_permission_cursor CURSOR FOR  
select folder_id from catalog.folders

OPEN grant_folder_permission_cursor   
FETCH NEXT FROM grant_folder_permission_cursor INTO @folder_id

WHILE @@FETCH_STATUS = 0   
BEGIN   
  
  EXEC [SSISDB].[catalog].[grant_permission] @object_type=@object_type_folder, @object_id=@folder_id, @principal_id=@principal_id, @permission_type=@permission_type_read
  EXEC [SSISDB].[catalog].[grant_permission] @object_type=@object_type_folder, @object_id=@folder_id, @principal_id=@principal_id, @permission_type=@permission_type_read_objects
  
  FETCH NEXT FROM grant_folder_permission_cursor INTO @folder_id
END
CLOSE grant_folder_permission_cursor   
DEALLOCATE grant_folder_permission_cursor 

-- grand read permision on projects in each folder
DECLARE @project_id bigint

DECLARE grant_project_permission_cursor CURSOR FOR  
select project_id from catalog.projects

OPEN grant_project_permission_cursor   
FETCH NEXT FROM grant_project_permission_cursor INTO @project_id

WHILE @@FETCH_STATUS = 0
BEGIN 

  EXEC [SSISDB].[catalog].[grant_permission] @object_type=@object_type_project, @object_id=@project_id, @principal_id=@principal_id, @permission_type=@permission_type_read

  FETCH NEXT FROM grant_project_permission_cursor INTO
@project_id
END
CLOSE grant_project_permission_cursor
DEALLOCATE grant_project_permission_cursor

 

sp_ReadErrorLog

sp_ReadErrorlog call examples

-- Reads SQL Server error log from ERRORLOG.1 file 
EXEC xp_ReadErrorLog 1

-- Reads current SQL Server error log 
EXEC xp_ReadErrorLog 0, 1

-- Reads current SQL Server Agent error log
EXEC xp_ReadErrorLog 0, 2

-- Reads current SQL Server error log with text 'Failed'
EXEC xp_ReadErrorLog 0, 1, 'Failed'

-- Reads current SQL Server error log with text ‘Failed’ AND 'Login'
EXEC xp_ReadErrorLog 0, 1, 'Failed', 'Login'

-- Reads current SQL Server error log with text ‘Failed’ AND ‘Login’ from 01-Jan-2016
EXEC xp_ReadErrorLog 0, 1, 'Failed', 'Login', '20160101', NULL

-- Reads current SQL Server error log with text ‘Failed’ AND ‘Login’ between 01-Jan-2012 and 30-Jan-2016
EXEC xp_ReadErrorLog 0, 1, 'Failed', 'Login', '20160101', '20160130'

-- Reads current SQL Server error between 01-Jan-2016 and 30-Jan-2016 
EXEC xp_ReadErrorLog 0, 1, NULL, NULL, '20160101', '20160130'

-- Reads current SQL Server error for the last 48 hours
declare 
  @datefrom datetime
,	@dateto datetime

select 
  @datefrom = dateadd(hour,-48,CONVERT(varchar, GETDATE(), 112))
,	@dateto = getdate()

EXEC xp_ReadErrorLog 0, 1, N'FAILED', NULL, @datefrom, @dateto, N'DESC'