using sql server security roles in ASP.net

Question

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

FrustratedWithFormsDesigner · Accepted Answer · 2011-03-21 13:58:11Z

1

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...

answered Mar 21, 2011 at 13:58

FrustratedWithFormsDesigner

27.7k31 gold badges151 silver badges211 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

AJ17 Over a year ago

the thing is, i do want to use windows authentication for my application but not for my sql server. so which means i do have to create my own sql user accounts for the roles in my application. if thats the case then i will have to store the sql user name and password within my application... which i am not sure is a good practice ... unless i encrypt it using aspnet_regiis. is this a good way of doing it or is there a better way?

FrustratedWithFormsDesigner Over a year ago

@AJ17: I think it's possible to use Windows authentication for the application and still have the database permissions the way I described, but I'm not exactly sure now, sorry. :(

AJ17 Over a year ago

thanks anyways.. would be nice if some senior members can share their experience about this :)

Philip Kelley · Accepted Answer · 2011-03-21 14:23:38Z

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.

Collectives™ on Stack Overflow

using sql server security roles in ASP.net

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related