I have couple of Active directory groups authenticated to access my RDS SQL Server instance. I have granted access to a AD group to perform ALTER ANY connection with GRANT.
When I log in as a user of that AD group and try to GRANT ALTER any connection the SQL stmt fails : GRANTOR does not have GRANT permission
But when I exclusively create a SQL login for the user in the group and give permission to that user and try the GRANT it works.
Is it supposed to work like that? If so, then what's the point of having a group? I'm not able to find any info on this. If you know any Microsoft document that explains this behavior it would be great.
Similar issue : AD User does not inherit the permission from AD Group – for a LOGIN
Best Answer
You need to use
AS principal.Ref: https://docs.microsoft.com/en-us/sql/t-sql/statements/grant-transact-sql
WITH GRANT OPTION
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.