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

  • Question

  • Hi All,

    I am not able to delete a user under SSISDB security (Users.) I am getting below error

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

    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 1

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

    Below queries are not giving any result:

    select permission_name,state_desc,object_name(major_id) as securable,
    user_name(grantor_principal_id) as grantor
    from sys.database_permissions
    where grantee_principal_id = user_id('UserName')

    select * from sys.database_permissions
    where grantor_principal_id = user_id('UserName')

    select * from sys.schemas where principal_id = user_id('UserName')

    Getting One record from below query:

    SELECT * FROM sys.[database_principals] where principal_id=user_id('UserNAme')

    name principal_id type type_desc default_schema_name create_date modify_date owning_principal_id sid is_fixed_role authentication_type authentication_type_desc default_language_name default_language_lcid
    UserName 13 U WINDOWS_USER dbo 31:45.3 31:45.3 NULL 0x0105000000000005150000005D28F57FD53AD8354354E02A59BD1A00 0 3 WINDOWS NULL NULL

    Please help. Thanks in advance.

    Regards,

    Avi


    -Regards Avi | Please kindly don’t forget to mark the post(s) that answered your question

    Friday, January 31, 2014 7:18 AM

All replies

  • Please check is that user owning any role using -

    SELECT     DBPRINCIPAL_1.NAME AS ROLE, DBPRINCIPAL_1.NAME AS OWNER
    FROM         SYS.DATABASE_PRINCIPALS AS DBPRINCIPAL_1 INNER JOIN
                     SYS.DATABASE_PRINCIPALS AS DBPRINCIPAL_2 
    		    ON DBPRINCIPAL_1.PRINCIPAL_ID = DBPRINCIPAL_2.OWNING_PRINCIPAL_ID
    WHERE     (DBPRINCIPAL_1.NAME = 'UserName') 

    Friday, January 31, 2014 8:03 AM
  • Hi

    Below query is also not returning any record. :(

    SELECT     DBPRINCIPAL_1.NAME AS ROLE, DBPRINCIPAL_1.NAME AS OWNER
    FROM         SYS.DATABASE_PRINCIPALS AS DBPRINCIPAL_1 INNER JOIN SYS.DATABASE_PRINCIPALS AS DBPRINCIPAL_2 ON DBPRINCIPAL_1.PRINCIPAL_ID = DBPRINCIPAL_2.OWNING_PRINCIPAL_ID
    WHERE     (DBPRINCIPAL_1.NAME = 'UserName')


    -Regards Avi | Please kindly don’t forget to mark the post(s) that answered your question

    Friday, January 31, 2014 11:37 PM
  • I would double-check this query:

    select * from sys.database_permissions
    where grantor_principal_id = user_id('UserName')

    Because that is what the error message is pointing at.

    It may also be worth to run this query:

    SELECT sm.definition
    FROM   sys.triggers t
    JOIN   sys.sql_modules sm ON t.object_id = sm.object_id
    WHERE  t.name = 'ddl_cleanup_object_permissions'

    And see what actually is on line 16 in that trigger. (I assume that it is a database trigger.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, February 1, 2014 9:47 AM
  • Hi,

    Use REVOKE to remove a grant or deny permission from a user on certain database objects.

    REVOKE [GRANT OPTION FOR] <permissions>
    ON <table>
    FROM <user/role>


    The following example revokes SELECT permission from the user RosaQdM on the table Person.Address in the AdventureWorks2012 database.

    USE AdventureWorks2012;
    REVOKE SELECT ON OBJECT::Person.Address FROM RosaQdM;
    GO

    For your reference:
    REVOKE Object Permissions (Transact-SQL)
    http://msdn.microsoft.com/en-us/library/ms187719.aspx

    Thanks.


    Tracy Cai
    TechNet Community Support


    • Edited by tracycai Monday, February 3, 2014 5:27 AM
    Monday, February 3, 2014 5:20 AM
  • I had the same problem in the SSISDB. Based on Erland's recommendation, I got the definition of the trigger from

    SELECT sm.definition 
    FROM   sys.triggers t
    JOIN   sys.sql_modules sm ON t.object_id = sm.object_id
    WHERE  t.name = 'ddl_cleanup_object_permissions'

    and found that the error was being raised in this bit

        IF EXISTS 
        (
            SELECT [grantor_sid]
            FROM [internal].[object_permissions]
            WHERE [grantor_sid] NOT IN
                (SELECT [sid] FROM [sys].[database_principals] where [sid] IS NOT NULL)
        )

    internal.object_permissions is a view that unions four tables. I ultimately found the offending permissions in one of these tables, specifically

    select op.*
    from internal.operation_permissions op
    inner join sys.database_principals p on op.[sid] = p.[sid]
    where p.name = 'username'

    The other three tables were internal.folder_permissions, internal.project_permissions, and internal.environment_permissions, so check those if internal.operation_permissions doesn't turn up anything.

    Because I had this problem in a dev environment, I didn't bother to research what those operation permissions were. I just took a quick backup and ran a DELETE on those rows. Voila, now I can drop the user. You may want to be a little more thorough in a prod environment, though.


    • Edited by Jenn Stauffer Tuesday, July 21, 2015 8:53 PM
    • Proposed as answer by Naomi N Monday, July 13, 2020 4:26 PM
    Tuesday, July 21, 2015 8:50 PM
  • I know this is 6 years later, but I was able to resolve this easily for a similar situation when trying to unmap a user from SSISDB.  It turns out that the user in question was given explicit permissions to one of the SSIS projects.  Unlike the other SSIS projects in the catalog that granted the dbo sql user all the permissions, this one project granted the permissions to a specific user.  After granting all the permissions to the dbo sql user and removing the permissions from the other login, I was able to unmap the login from SSISDB.

    To access the permissions for a SSIS project, just right-click on the project to bring up the subcontext menu.  Then choose permissions to see who has what permissions to the project.

    • Proposed as answer by J I M B O Monday, July 13, 2020 3:31 PM
    Monday, July 13, 2020 3:31 PM