SQLTeam.com | Weblogs | Forums

Grant, With Grant, Deny

So, I understand GRANT and DENY already. But when will there be a case scenario in need of WITH GRANT or WITH GRANT OPTION? I have read this from Microsoft already:

The GRANT ... WITH GRANT OPTION specifies that the security principal receiving the permission is given the ability to grant the specified permission to other security accounts. When the principal that receives the permission is a role or a Windows group, the AS clause must be used when the object permission needs to be further granted to users who are not members of the group or role. Because only a user, rather than a group or role, can execute a GRANT statement, a specific member of the group or role must use the AS clause to explicitly invoke the role or group membership when granting the permission. The following example shows how the WITH GRANT OPTION is used when granted to a role or Windows group.

1 Like

If you want that user to be able to grant that access to other users. For example, you have a group of BI analysts who will be managing a set of tables/objects (they create new tables, views, procedures, etc...) - and they want to give a different group read access to their objects.

1 Like