I've set up the following scenario:
The Demo_DB is owned by the sysadmin role. The Demo_Schema was created and is owned by Demo_Developer role. All the objects in Demo_Schema are owned and have been created by the Demo_Developer role.
AccountAdmin created a new role called Demo_Analyst, which the intent is to set up as a read-only user of the Demo_Schema tables. Demo_Developer successfully executed the following grant (assuming because it is the owner of all the tables in the Demo_Schema):
use role Demo_Developer;
grant select on all tables in schema Demo_DB.Demo_Schema to role Demo_Analyst;
However, neither Demo_Developer or event SysAdmin (which belongs to the Demo_Developer role) can execute the following:
use role sysadmin;
grant select on FUTURE tables in schema Demo_DB.Demo_Schema to role Demo_Analyst;
However, the above will execute using the AccountAdmin role. This seemed strange to me at first, but then I reasoned that maybe we won't know which future tables will be created by which owners, so we need an AccountAdmin to grant FUTURE. Is that correct?
I did some more testing and changed ownership of one of the tables in Demo_Schema to a completely different role that Demo_Developer isn't a part of. Then I tried to grant select on all tables using that Demo_Developer again (first statement above), and this time it didn't grant to all tables, but just to the tables Demo_Developer was the owner of.
So I wonder why the attempted grant on FUTURE tables doesn't work the same way - i.e. grant select on future tables owned by the grantor of the future grant.
So my question is: Why do I need an AccountAdmin to grant select on Future tables, but I can use the owner role (Demo_Developer) to grant select on all current tables?
