Menu
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.
749
The REVOKE statement can be used to remove granted permissions, and the DENY statement can be used to prevent a principal from gaining a specific permission through a GRANT. Granting a permission removes DENY or REVOKE of that permission on the specified securable.
To revoke all privileges, use the second syntax, which drops all global, database, table, column, and routine privileges for the named users or roles: REVOKE ALL PRIVILEGES, GRANT OPTION FROM user_or_role [, user_or_role] ... REVOKE ALL PRIVILEGES, GRANT OPTION does not revoke any roles.
Use the DELETE privilege type to revoke permission to delete rows from the specified table. Use the INSERT privilege type to revoke permission to insert rows into the specified table. Use the REFERENCES privilege type to revoke permission to create a foreign key reference to the specified table.
You can also revoke this access by unchecking the GRANT permission for ALTER TRACE in the same window. So to do that follow the same process which we did in step 3 and step 4, if the GRANT permission is checked you should uncheck the tick in the GRANT permission for ALTER trace and click the OK button.
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
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!).
28
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
