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

Question

question

Sahuatoba asked Jan 21, '10

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-2005 permissions

1

10 |1200

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

graz ♦ commented · Jan 21, 2010 at 10:05 PM

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 ·

Answer 1 · 2010-01-22T13:25:14Z

Matt Whitfield answered Jan 22, '10

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

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

Answer 2 · 2010-01-22T21:12:15Z

Sahuatoba answered Jan 22, '10

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

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

question