none
DROP user fails with "The database principal has granted or denied permissions to catalog objects in the database and cannot be dropped."

    Question

  • running SQL SERVER 2012

    This is a strange one!  I have a database user I'm trying to drop from a database.  It's the SSIS catalog database.  When I try to drop the user I get the follwing mesage

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

    From what I can tell the user has not issued grants to any objects.  I queried the "sys.database_permissions" for the SSIS database.  I checked both the "user_name(grantor_principal_id)" and "user_name(grantee_principal_id)" and the user is not listed.

    I confirmed the user does not own a schema either and the user is not in any of the database roles.

    Any ideas what else could be causing this?

    Thanks!

    • Edited by mikea730 Friday, September 07, 2012 10:55 PM
    Friday, September 07, 2012 10:49 PM

Answers

  • You have to remove any database permissions and permissions that were granted in the SSIS catalog.  I think there is a trigger that stops you from dropping the user if they have any permissions defined in the SSISDB catalog.  Here is the query I used to find those permissions and generate the catalog.revoke_permission statements.  After running those, I was able to drop the user.

    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)
      FROM [SSISDB].[catalog].[explicit_object_permissions]
      WHERE principal_id = USER_ID('Your User')
    Dave

         


    Dave DuVarney

    • Marked as answer by mikea730 Friday, March 14, 2014 4:14 PM
    Friday, March 14, 2014 3:42 PM

All replies

  • Share the query you are using to check the same. Try to run 2 below queries on database for your user.

    1)

    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')   

    2)

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


    Please click the Mark as Answer or Vote As Helpful if a post solves your problem or is helpful!

    Sunday, September 09, 2012 10:32 AM
  • Any ideas what else could be causing this?

    Thanks!

    The error message means that the problem user has granted permissions to catalog objects.  So if the query you used to identify permissions joined with sys.objects, it will not show those permissions.

    I would expect the second query RohitGarg posted to return the permissions granted.  Below is an example script that will generate the needed REVOKE statements.

    SELECT
    	N'REVOKE ' 
    	+ permission_name + 
    	N' ON '
    	+ QUOTENAME(SCHEMA_NAME(OBJECTPROPERTY(p.major_id, 'SchemaId')))
    	+ N'.'
    	+ QUOTENAME(OBJECT_NAME(p.major_id)) 
    	+ N' FROM ' 
    	+ QUOTENAME(USER_NAME(p.grantee_principal_id)) 
    	+ N' CASCADE AS user1;'
    FROM sys.database_permissions AS p
    WHERE 
    	grantor_principal_id = USER_ID('user1')
    	AND OBJECTPROPERTY(p.major_id, 'SchemaId') = SCHEMA_ID('sys');
    


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    Sunday, September 09, 2012 2:25 PM
    Moderator
  • Thanks for the previous 2 postings.  But as mentioned, I've already confirmed the user is not listed in "sys.database_permissions" for the database.  I even did the below statement to list all unique combinations and the user is not listed.

    select distinct
         user_name(grantor_principal_id) as grantor,
      user_name(grantee_principal_id) as grantee                  
    from ssisdb.sys.database_permissions  

    Thanks!

    Monday, September 10, 2012 7:24 PM
  • I'm am getting the same error when trying to drop a user from the SSIS database on a dev server (SQL Server 2012 SP1 - 11.0.3000).

    I was playing with the security of the account, granting and denying permissions in the SSISDB. I have removed all permissions and sys.database_permissions returns 0 rows for the user, but I still can't delete the user.

    Tuesday, January 15, 2013 1:26 AM
  • I managed to delete my user from SSISDB, but it's not elegant. Use with caution.

    DELETE FROM [SSISDB].[internal].[operation_permissions] WHERE USER_NAME(sid) IS NULL;
    GO

    DELETE FROM [SSISDB].[internal].[project_permissions] WHERE USER_NAME(sid) IS NULL;
    GO

    DELETE FROM [SSISDB].[internal].[folder_permissions] WHERE USER_NAME(sid) IS NULL;
    GO

    DELETE FROM [SSISDB].[internal].[environment_permissions] WHERE USER_NAME(sid) IS NULL;
    GO

    • Proposed as answer by TeutenbergNZ Tuesday, January 15, 2013 2:29 AM
    Tuesday, January 15, 2013 2:29 AM
  • Can you help me with this?
    Thursday, October 03, 2013 3:04 PM
  • I believe that the scripts given were backwards. This error means that the user cannot be dropped because IT granted permissions to another user that still exists. Until those permissions are changed, the said user cannot be dropped; i.e. foreign key from GRANTOR on a user to the said user. Try this instead:

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

    Monday, November 04, 2013 1:25 PM
  • Why ssisd catalog? Please follow Dan's instruction. I was able to reproduce the issue and resolve it by running Dan's query.

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, November 05, 2013 7:53 AM
  • You have to remove any database permissions and permissions that were granted in the SSIS catalog.  I think there is a trigger that stops you from dropping the user if they have any permissions defined in the SSISDB catalog.  Here is the query I used to find those permissions and generate the catalog.revoke_permission statements.  After running those, I was able to drop the user.

    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)
      FROM [SSISDB].[catalog].[explicit_object_permissions]
      WHERE principal_id = USER_ID('Your User')
    Dave

         


    Dave DuVarney

    • Marked as answer by mikea730 Friday, March 14, 2014 4:14 PM
    Friday, March 14, 2014 3:42 PM