0

How do I give access to a user so that they can grant SELECT and 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 SELECT and 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

  • How do you know which user created which object(s)? SQL Server does not track the principal_id of the user that creates anything. It only tracks the create_date and modify_date. – Solomon Rutzky Mar 5 at 17:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.