Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
I'm working on a project that requires a database user to have only SELECT and INSERT permissions for all tables in a database.
The following question provides a solution but I'd have to run the script every time I add a new table. Seems like it's specific to SQL Server 2000.
There must be an easier way?
In my case, I have only a single schema dbo. So, instead of adding permissions to each table, it's easier to add it to the schema.
GRANT SELECT, INSERT ON SCHEMA::dbo to [my_user]
This way, each time a table is added to that schema the permissions will be applied automatically. For all my projects the schema rarely change (compared to the number of times a new table is added) so it's acceptable to run this one if a new schema is added.
Maybe other answers would provide a solution where the permissions can be applied at the database level?
GRANT SELECT, INSERT to [my_user]
Aug 31, 2017 at 19:01