using sql server security roles in ASP.net

I am building an Intranet(with asp.net c#) application which uses windows authentication. in my application there will be three roles. and these three roles will have different privileges in the sql server 2008 which will be connected with it.

in the sql server 2008, i plan to create 3 different security roles which have different user privileges in the DB. i.e they will have different read write permission for different tables in the database.

my question to you all are, is there a way for me to map the application roles to the sql server roles?

i did do some research into this, but the information that i found was more regarding "creating a sqlserviceprovider"... which was not exactly what i was looking for.

any suggestions, recommendations will be开发者_如何学运维 most welcomed.

thanks adrian

I don't know for sure about how it's done on MS SQL Server, but in general, application-oriented security roles for application-users are not tied to database users. Usually what I see happening is a single database user created for the application to use to make the connection, and then everything else related to security is handled through a "users-permissions" table which tells the application what permissions different application-users have. It might be possible to map application-users directly to database-users, but I once heard it suggested and saw both DBA's in the room cringe...

What @FrustratedWithFormsDes said.

But, if you have to do something like this, you could maybe hack something together using SQL Servers' Application Roles. This is a long and involved topic, so the brief overview is: you can use a (SQL) application role + password to reset the permissions and priviliges of the current databse user (i.e. resets rights for a SQL login in one database). Later versions (definitely 2008) allow you to "logout" of the app role login, and re-login as a different application role.

Note, however, that if you are using connection pooling, application roles can really mess you up.

For details check out Application Roles in Books Online (err, online here), and Googling "SQL Server Application Roles" will turn up any number of articles.