How do I give access to a user so that they can grant
VIEW DEFINITION on any objects they create in a database.
For example, I have a user
MyUser in the roles
db_datareader db_datawriter db_ddladmin
and that user can create a table:
CREATE TABLE Test123 ( a INT )
but I want the user to then be able to grant
VIEW DEFINITION only on the table they have just created (and any others they create as they create them)
GRANT SELECT ON Test123 TO [AnotherUser] GRANT VIEW DEFINITION ON Test123 TO [AnotherUser] GO
What are my options to achieve this?
The only way I can think of is to have the user create objects in a schema and have them as the schema owner but ideally, I would like the objects to be created in the user's current default schema