question

Sahuatoba avatar image
Sahuatoba asked

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

Hi, i have a user in sql server database. I try drop user, but I get the follow error:

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

I Run the following query:

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

with 0 results.

Then run the following query:

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

with the follow 18 results:

class class_desc | major_id | minor_id |                     
grantee_principal_id | grantor_principal_id |                     
type permission_name | state | state_desc                    
                    
4  DATABASE_PRINCIPAL 7 0 5 7  AL   ALTER       	D     DENY                    
                    
4  DATABASE_PRINCIPAL 7 0 5 7  CL   CONTROL 	G     GRANT                    
                    
4  DATABASE_PRINCIPAL 7 0 5 7  VW   VIEW DEFINITION     G     GRANT                    

. . .

(18 rows)

Then i run the following query:

REVOKE VIEW DEFINITION ON USER::PORTALWEB TO public                    
REVOKE CONTROL ON USER::PORTALWEB TO public                    
REVOKE ALTER ON USER::PORTALWEB TO public                    

But i run again

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

shows me the 18 same records and i cant drop user PORTALWEB from my DB Please, any help?

Thanks!!!

sql-server-2005permissions
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Your query doesn't show what actual objects have permissions granted. Try this: select object_name(major_id), * from sys.database_permissions . Edit your question to include all the lines returned and not just the first few.
0 Likes 0 ·
Matt Whitfield avatar image
Matt Whitfield answered

What you want to revoke is the permissions that the user has granted, rather than permissions on that user.

REVOKE permission ON object TO grantee            

permission comes from the permission_name column

object is the object that the permissions are granted on. Given what you've shown, it looks like the user portalweb has granted permissions to another user to perform operations on the portalweb user

grantee is the name of the user that permissions were granted to. In this case, the name of the user with ID 5.

So you're looking at something like

REVOKE ALTER ON USER::portalweb TO *grantee*            
REVOKE CONTROL ON USER::portalweb TO *grantee*            
REVOKE VIEW DEFINITION ON USER::portalweb TO *grantee*            

Which is pretty much what you had, so props for that. Only step left is to find the correct grantee - which will be given by this query:

SELECT * FROM sys.[database_principals] WHERE [principal_id] = 5            
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Sahuatoba avatar image
Sahuatoba answered

Thank you both, you show me the light!!!!!

I run the query SELECT * FROM sys.[database_principals] WHERE [principal_id] = 5 (thank you Matt Whitfield).

with many records, but only 5 differences in the column grantee_principal_id: (5,6,9,10,11,12)

I run query: SELECT * FROM sys.[database_principals] WHERE [principal_id] IN (5,6,9,10,11,12)

And when i got the users name, i remove all permissions. Then i try run: drop user PORTALWEB with success

thank you very much

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.