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

Question

Post reply

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

tt-615680

SSCertifiable

Points: 7835
More actions
August 8, 2016 at 11:14 am

#328644

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!

Viewing 15 posts - 1 through 15 (of 15 total)

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

Sue_H SSC Guru Points: 90860 More actions · Answer 1

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 SSCertifiable Points: 7835 More actions · Answer 2

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 SSC Guru Points: 148809 More actions · Answer 3

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 SSCertifiable Points: 7835 More actions · Answer 4

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 SSC Guru Points: 148809 More actions · Answer 5

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

DROP USER USERNAME

John

tt-615680 SSCertifiable Points: 7835 More actions · Answer 6

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 SSCertifiable Points: 7835 More actions · Answer 7

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 SSC Eights! Points: 864 More actions · Answer 8

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 SSCertifiable Points: 7835 More actions · Answer 9

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

Sue_H SSC Guru Points: 90860 More actions · Answer 10

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

Sue_H SSC Guru Points: 90860 More actions · Answer 11

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

mnadirwk SSC-Addicted Points: 476 More actions · Answer 12

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,

adey11 SSC Veteran Points: 282 More actions · Answer 13

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

tung858 SSC Veteran Points: 241 More actions · Answer 14

Just set all grantor to SA.

Who really minds who granted what permission? It's going to be a DBA with sysadmin access doing it.

update internal.project_permissions

set grantor_sid = 0x01

Tung Dang
Azure and SQL Server DBA Contractor / Consultant
DataZip