CODEWITHSUNDEEP
sql-serversql-server-2008deprecated
Craig
Craig

Reputation: 4239

How to replace REVOKE ALL in SQL Server 2008 R2

How do I replace the ALL permission now that it has been revoked: 

REVOKE ALL ON dbo.MyObject TO MyUser

I'm looping through all the objects in a database and revoking all permissions for a specific user. So if I had to be specific about which permission to revoke, it would be a real hassle because I would have to find out what object I have and then revoke every possible permission for that type of object. Instead of just REVOKE ALL.

Upvotes: 16

Views: 16109

Answers (3)

PollusB
PollusB

Reputation: 1816

You can't revoke all on schema::dbo to your user but you can...

REVOKE select,update,delete,insert,execute,references on schema::DBO to USER

Upvotes: 8

Ben
Ben

Reputation: 35643

You really need to have some idea what permissions you want the user to have. the pattern you have there is "Just revoke everything, then later put back whatever was there before...".

If you really need to do that, then you will have to keep your own record of what the permissions were to begin with.

A better solution would be to have some idea what the permissions ought to be. Who knows? They may have been wrong before!

Upvotes: -1

Ben Thul
Ben Thul

Reputation: 32737

If you want to get a list of securables to which a database principal has been granted permission, look no further than sys.database_permissions. How to interpret the major_id and minor_id columns depends on the value of the class column (for instance, if class = 1, then major_id = object_id and minor_id = column_id). I leave that as an exercise to the reader (check out the BOL entry for it here: http://msdn.microsoft.com/en-us/library/ms187719.aspx).

Another option might be just to drop and re-add the principal. Dropping the principal would get rid of any permissions associated directly to it and re-adding it doesn't grant any (unless you specifically grant some). That might be another option (but test it first!).

Upvotes: 2

Related Questions