Unable to drop a user on SSISDB

Yes, this is a known issue when trying to drop a user from the SSISDB in SQL Server. The error you are seeing, means the user you're trying to drop has granted or denied permissions on objects within the SSISDB catalog, and due to built-in triggers, SQL Server blocks the drop operation to avoid breaking permission chains.

Root Cause:

SSISDB has internal DDL triggers (e.g., ddl_trg_disable_user) that prevent dropping users who have granted/denied access to SSIS objects (projects, packages, environments, etc.).

Suggested Approach:

1. Check which permissions the user has granted or denied in SSISDB.

SELECT * 
FROM catalog.object_permissions
WHERE grantee_sid = SUSER_SID('your_user_name')
   OR grantor_sid = SUSER_SID('your_user_name')

2. Revoke or transfer those permissions: For each row returned, you must either.

Revoke the permission or reassign it to another principal.

Example:

USE SSISDB;
REVOKE [READ] ON [project]::[YourFolder\YourProject] TO [your_user_name]

3. Double-check for remaining references.

SELECT * 
FROM catalog.folders 
WHERE created_by_sid = SUSER_SID('your_user_name')

If the user created any folders, they may still be referenced.

4. After clearing permissions, drop the user.

USE SSISDB;
DROP USER [your_user_name]

Hi Noma_Putumo​ ,

this usually happens when the user has been granted permissions on SSISDB catalog objects. You will need to first check what permissions the user has using:-

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];

There is a database trigger "ddl_cleanup_object_permissions" in the SSISDB on  DROP_USER event.

It checks if the user has some object permissions.