Allowing Other Accounts to Grant Object Permissions

When you grant an object permission to a user account in a database, you can optionally specify the WITH GRANT OPTION clause, which allows the user account to grant that object permission to others. A user account can be a Microsoft® Windows NT® 4.0 or Windows® 2000 user or group or a Microsoft SQL Server™ user or role.

For example, if you use the WITH GRANT OPTION clause when you grant permissions on the salaries table to the user user_a, user_a is able to grant the same permissions on the table to any other user account in the database. For groups and roles, if you grant permissions on the salaries table to role role_a specifying the WITH GRANT OPTION clause, each member of role_a can grant the object permission to any other user account, provided that the AS clause of the GRANT statement is specified. For more information, see GRANT.

Important  When you use the WITH GRANT OPTION clause, you have no future control over which security accounts will receive that permission.

When you revoke a permission granted using the WITH GRANT OPTION clause, specify the CASCADE clause to have the permissions revoked from the user account as well as any other accounts that received the permission from the initial account.

For example, you have granted a permission specifying WITH GRANT OPTION to the user user_a. User_a granted the permission specifying WITH GRANT OPTION to the user user_b, and user_b granted the permission to the user user_c. User_a has left the company, but SQL Server does not allow you to remove a user account if it has granted a permission specifying the WITH GRANT OPTION clause to another account. Specifying the WITH GRANT OPTION clause has created a chain from user_a through user_b to user_c. You cannot remove the account for user_a until the permissions are revoked for user_b and user_c. When you revoke the permission from user_a and specify the CASCADE option, the permission is removed from the user_a, user_b, and user_c accounts. You then may remove the user_a account.