revoke grant option for revokes the user’s ability to grant the specified permission to other users, but does not revoke the permission itself from that user. If the user has granted that permission to others, you must use the cascade option; otherwise, you receive an error message and the revoke fails.
revoke grant option for select on titles from bob cascade
If Bob has not granted this permission to other users, this command revokes his ability to do so, but he retains select permission on the titles table.
If Bob has granted this permission to other users, you must use the cascade option. If you do not, you receive an error message and the revoke fails. cascade revokes this select permission from all users to whom Bob has granted it, as well as their ability to grant it to others.
You cannot use revoke with the cascade option to revoke privileges granted by the table owner. For example, the owner of a table (UserA) can grant privileges to another user (UserB) as in this scenario:
create table T1 (...) grant select on T1 to UserB
However, the system administrator cannot revoke UserB’s privileges using the revoke privileges command with the cascade option as in this statement:
revoke select on T1 from UserA cascade
This statement revokes the select privileges of the table owner, but does not revoke those privileges from UserB.
By default, all data manipulation language (DML) operations are revoked implicitly for users other than the table owner (except for decrypt permission when restricted decrypt permission is enabled. See the Encrypted Columns Users Guide). Because the sysprotects table contains no records indicating that the table owner has granted and then revoked privileges, the cascade option is not invoked.You must revoke explicitly the select privilege from UserB.