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

  • Dear all,

    I get the following error message when trying to remove a login on SSISDB even though it has no longer any permissions on the database:

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

    Would you please let me know what I should be doing?

    Thank you in advance!

    You cannot vote on your own post

    0

    Dear all,

    I get the following error message when trying to remove a login on SSISDB even though it has no longer any permissions on the database:

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

    Would you please let me know what I should be doing?

    Thank you in advance!

  • The error indicates the user has granted permissions to catalog objects. Have you checked for those permissions?

    select *

    from sys.database_permissions

    where grantor_principal_id = user_id ('USERNAME');

    You would need to revoke those permissions and then drop the user.

    Sue

  • Thank for you reply!

    When I run the following query with the username I get 0 results which is why I'm confused to why it cannot be removed.

    select *

    from sys.database_permissions

    where grantor_principal_id = user_id ('USERNAME');

    Is there any chance that it could be that because the particular user is an owner of another database, that is the only thing I can think of?

  • You have replaced "USERNAME" in that query with the name of the login you can't drop, haven't you? And you are running the query in the correct database context (SSISDB)? What does this return?SELECT DISTINCT(SUSER_NAME(grantor_principal_id))

    FROM SSISDB.sys.database_permissions

    John

  • John Mitchell-245523 (8/9/2016)


    You have replaced "USERNAME" in that query with the name of the login you can't drop, haven't you? And you are running the query in the correct database context (SSISDB)? What does this return?SELECT DISTINCT(SUSER_NAME(grantor_principal_id))

    FROM SSISDB.sys.database_permissions

    John

    Thank you for your reply!

    Yes, I have replaced the 'username' with the login I need to remove but no results are returned.

    When I run the following script I get 'sa' result back

    SELECT DISTINCT(SUSER_NAME(grantor_principal_id))

    FROM SSISDB.sys.database_permissions

    Thank you

  • Do you still get the error if you try to remove the user like this? USE SSISDB

    DROP USER USERNAME

    John

  • John Mitchell-245523 (8/9/2016)


    Do you still get the error if you try to remove the user like this? USE SSISDB

    DROP USER USERNAME

    John

    When trying to drop the required user using the above script, I get the following error message:

    Msg 27226, Level 16, State 1, Procedure ddl_cleanup_object_permissions, Line 18

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

    Msg 3609, Level 16, State 2, Line 3

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

  • tt-615680 (8/9/2016)


    John Mitchell-245523 (8/9/2016)


    Do you still get the error if you try to remove the user like this? USE SSISDB

    DROP USER USERNAME

    John

    When trying to drop the required user using the above script, I get the following error message:

    Msg 27226, Level 16, State 1, Procedure ddl_cleanup_object_permissions, Line 18

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

    Msg 3609, Level 16, State 2, Line 3

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

    Yes, I still get the error by running the script

  • tt-615680,

    In SSISDB, internal.object_permissions is a view that unions four tables.

    Those tables are:

    internal.operation_permissions

    internal.folder_permissions

    internal.project_permissions

    internal.environment_permissions

    Check each of those tables for entries for the user you want to drop and remove them from these tables first and then try your drop user operation again.

    As always, back up your tables before dropping any rows.

    Hope this helps!!

    Jon

  • SeniorITGuy (8/9/2016)


    tt-615680,

    In SSISDB, internal.object_permissions is a view that unions four tables.

    Those tables are:

    internal.operation_permissions

    internal.folder_permissions

    internal.project_permissions

    internal.environment_permissions

    Check each of those tables for entries for the user you want to drop and remove them from these tables first and then try your drop user operation again.

    As always, back up your tables before dropping any rows.

    Hope this helps!!

    Jon

    Thank you for your reply, I want to know how I'm able to find the user I'm trying to remove as the script I run earlier did not work e.g. the sid or grantor_id?

    Thank you

  • Don't just check for the specific user name but also check using

    WHERE USER_NAME(sid) IS NULL

    I've seen several people hit this same issue and the user is null on the internal tables, views.

    Sue

  • Don't just check for the specific user name but check also for rows where the user_name(sid) is null as that can be part of the problem.

    Sue

  • tt-615680 - Tuesday, August 9, 2016 7:41 AM

    SeniorITGuy (8/9/2016)


    tt-615680, In SSISDB, internal.object_permissions is a view that unions four tables. Those tables are: internal.operation_permissions internal.folder_permissions internal.project_permissions internal.environment_permissions Check each of those tables for entries for the user you want to drop and remove them from these tables first and then try your drop user operation again. As always, back up your tables before dropping any rows. Hope this helps!! Jon

    Thank you for your reply, I want to know how I'm able to find the user I'm trying to remove as the script I run earlier did not work e.g. the sid or grantor_id? Thank you

    Read this blog
    https://blog.dbi-services.com/delete-an-orphan-user-database-under-ssisdb/
    wel, in the end, you have to go --> Integration Service Catalogs--> SSISDB-->(EACH FOLDER)-->(EACH PROJECT)RIGHT CLICK-->Properties-->Permission. Take out all permission on Explicit tabe. click ok.

    Thanks,

  • You can find the sid with:-
    select * from sys.sysusers
    where
        [name] like 'domain\domainuser'

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply