Unable to Drop a User from SSISDB?

I ran into an issue when trying to drop a user from SSISDB (the Integration Services Catalog database). 

The database principal has granted or denied permissions to catalog objects in the database and cannot be dropped.

The transaction ended in the trigger. The batch has been aborted.

How to solve it?

Find where the user has permissions

USE SSISDB

go

SELECT *

FROM catalog.object_permissions

WHERE grantee_sid = SUSER_SID('YourUserName');

--change YourUserName to your actual username

Revoke permissions

REVOKE READ ON OBJECT::[folder_name] TO [YourUserName];

REVOKE MODIFY ON OBJECT::[project_name] TO [YourUserName];

Drop the user

DROP USER [YourUserName];


No comments:

Post a Comment