SSIS error: Drop faield for user

I was trying to drop a user from SSIDB database and I encountered the following error -

The database principal has granted or denied permission to catalog objects in the database and cannot be dropped. The transaction ended in the trigger. The batch has been aborted. (Microsoft SQL Server, Error: 27226)


I ran the following query to find the permission of the user I am trying to drop


use SSISDB
GO

SELECT TOP 1000 [object_type]

,[object_id]

,[principal_id]

,[permission_type]

,[is_deny]

,[grantor_id]

, 'EXEC catalog.revoke_permission @object_type=' + CAST([object_type] AS VARCHAR)

+ ', @object_id=' + CAST([object_id] AS VARCHAR)

+ ', @principal_id=' + CAST(principal_id AS VARCHAR)

+ ', @permission_type=' + CAST(permission_type AS VARCHAR) [execute this]

FROM [SSISDB].[catalog].[explicit_object_permissions]

WHERE principal_id = USER_ID('usertodrop')


Then copied the contents of the [execute this] column in a new query window and executed it against the SSISDB database which revokes the permission .
Then I was able to drop the user.

Error logging in SSIS

A very cool feature where the error from SSIS can be logged with few steps. Right click on the control flow canvas and select Logging.
In the Add a new log section you can select a Provider type. I have selected the SSIS log provider for SQL Server since I want to log the error to the database.  The ErrorLogConn  is the Environment variable that I have created in Project level. ( This points to the msdb database where I want to log the error)

In the following screen you get to select the events that you want to log for. I have selected only the following events keeping in mind that selecting all events might fill up the database soon.

OnError
OnTaskFailed
OnWarning

Once you hit OK an save the project, deploy it and that's all.

 

 

You can run the following to see the last error

 

 

SELECT TOP (100) PERCENT PKG.PackageName, PKG.starttime AS PackageStartTime, LG.source AS TaskName, LG.starttime AS StartTime, LG.endtime AS EndTime,

LG.message

FROM dbo.sysssislog AS LG INNER JOIN

(SELECT LG1.executionid, LG1.source AS PackageName, LG1.starttime

FROM dbo.sysssislog AS LG1 INNER JOIN

(SELECT source, MAX(starttime) AS starttime

FROM dbo.sysssislog

WHERE (event = 'PackageStart')

GROUP BY source

HAVING (MAX(starttime) > DATEADD(dd, -1, GETDATE()))) AS CUR ON CUR.source = LG1.source AND CUR.starttime = LG1.starttime

WHERE (LG1.event = 'PackageStart')) AS PKG ON LG.executionid = PKG.executionid

WHERE (LG.event IN ('OnError'))

ORDER BY PackageStartTime DESC, StartTime

 

(The above query was taken from the blog http://dataqueen.unlimitedviz.com/2012/08/logging-in-ssis/ - an awesome blog!)