Create user with only SELECT and INSERT permissions

Question

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.

• How do I create a user in SQL-Server that only has access to one table, and can only insert rows

There must be an easier way?

Answer 1

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?