SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
tt-615680
tt-615680
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4362 Visits: 1272
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!
Sue_H
Sue_H
SSC-Dedicated
SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)

Group: General Forum Members
Points: 33185 Visits: 9455
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



tt-615680
tt-615680
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4362 Visits: 1272
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?
John Mitchell-245523
John Mitchell-245523
SSC Guru
SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)

Group: General Forum Members
Points: 79698 Visits: 17892
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
tt-615680
tt-615680
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4362 Visits: 1272
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
John Mitchell-245523
John Mitchell-245523
SSC Guru
SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)

Group: General Forum Members
Points: 79698 Visits: 17892
Do you still get the error if you try to remove the user like this?
USE SSISDB
DROP USER USERNAME


John
tt-615680
tt-615680
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4362 Visits: 1272
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
tt-615680
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4362 Visits: 1272
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
SeniorITGuy
SeniorITGuy
Mr or Mrs. 500
Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)

Group: General Forum Members
Points: 532 Visits: 125
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
tt-615680
tt-615680
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4362 Visits: 1272
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search