June 23, 2010 at 12:58 pm
We are attempting to delete a user from an imported database and receive the following error when doing so:
Msg 15284, Level 16, State 1, Line 3
The database principal has granted or denied permissions to objects in the database and cannot be dropped.
class class_desc major_id minor_id grantee_principal_id grantor_principal_id type permission_name state state_desc
17 SERVICE 65599 0 10 9 SN SEND G GRANT
Querying the sys.services view it appears as though the object in question is some service but we haven't been able to delete it yet.
SqlQueryNotificationService-8bb648a3-b219-4fe5-845a-1dc8091a450b655411796086868
SqlQueryNotificationService-a71fcf07-a216-4105-afe4-8b1ebdd71a63655421844087039
[highlight=#ffff11]SqlQueryNotificationService-c982ce81-08cc-4d51-b4a9-919553586f736559991755178790[/highlight]
http://schemas.microsoft.com/SQL/Notifications/EventNotificationService211241940392
http://schemas.microsoft.com/SQL/Notifications/QueryNotificationService111209940278
http://schemas.microsoft.com/SQL/ServiceBroker/ServiceBroker311273940506
How can we determine what the user has granted privileges on and either revoke them or delete the object so that we can remove them from the database?
June 23, 2010 at 3:02 pm
I would start by trying...
select *
from TABLE_PRIVILEGES
where GRANTOR = 'offending_account'
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply