What permissions should a SQL Server user provide for LinqToSql?

Question

I use LinqToSQL and a trusted connection to handle a database that selects / updates / inserts / deletes. In the past, I always used stored procedures and only granted execute permission for the application pool identifier for a particular sproc in the database. This is my first LinqToSql project (and I really like how much this helps). I would prefer not to give dbo access to the application pool identifier in order to get LinqToSql to work (but if recommended, I don't mind). What types of permissions can I grant an application pool identifier so that LinqToSql has minimal permissions? Or should I just go with dbo permissions and do with it?

According to KristoferA's answer , these are the permissions I granted to the application pool identifier in the database:
EXEC sp_addrolemember 'db_datareader', 'app_pool_identity'
EXEC sp_addrolemember 'db_datawriter', 'app_pool_identity'

The security level is not exactly the same as granting execution permissions for the necessary sprocs, but I am very good at this, given the huge development achievements that I have achieved only through the use of Linq2SQL. And this is better than giving full access to dbo.

Answer 1

db_datareader and db_datawriter are enough if you just want to read and write data without going into schema changes and more ...

Answer 2

LinqToSQL creates dynamically created SQL. Thus, he needs full access to the commands of the data management language (insert, update, delete). Your application account should not have access to data definition language commands (create, delete, modify, etc.).