Permission rights for SQL login accessing Database for ASP.Net Application

Question

I always wonder what are the exact access rights and permissions I need to give to a sql login which I use from my asp.net application to access database. The application execute some stored procedures which insert, update and delete data into tables. I do select, delete, update directly on the tables also. Also there are some triggers. Wonder if there is a comprehensive list of the permission matrix to help.

Answer 1

Well, it depends on how complicated you want to make it :-)

Simplest solution:

• make your login / db user have the db_datareader role to read all tables
• make your login / db user have the db_datawriter role to write all tables

As for executing stored procs, what we did is create a new custom database role "db_executor" in our database like this:

CREATE ROLE [db_executor] AUTHORIZATION [dbo]
GRANT EXECUTE TO [db_executor]

and then we grant this role to the db user as well. This new custom database role will have execute rights on all existing AND on all future stored procs/funcs in your database.

With this, your db user can read and write any table and execute any stored proc and stored func.

More complex solution: You can of course also GRANT permissions on individual tables, views, procs, funcs to inidividual db users and/or db roles. But it can get quite messy and complicated.

Marc