Using SQL Server Users and Roles as an authorization database for an intranet web application?

I have a question that really feels like I should have an easy answer to, but for one reason or another I haven't been able to totally reason around it.

I'm embarking on development of an ASP.NET MVC3 intranet application, and I'm currently working on designing authentication & authorization. We're forced to use basic authentication in our environment, and we use Active Directory, so the authorization part is generally taken care of. Unfortunately ou开发者_运维技巧r role/user hierarchy in active directory doesn't mirror what I need for the roles in the application, so I'm going to have to define my own.

I'm using SQL Server, so I was originally thinking of using stored procedures for all DML, and then creating roles and adding users in roles in SQL Server, and then controlling access to the stored procedures via those roles. I was also thinking I could query for those SQL Server database-level users & roles in order to use that as the source of authorization info in the application itself. That originally seemed like a great idea, but it doesn't seem like a popular one (for one, it seems the queries for that are a little long and messy for what they produce). Alternatively, would it be better to have the web app impersonate a user for all queries to the server, and then implement a user/role database with my own schema, and only authorize on the application side?

It originally seemed that authorizing on both the application and database side would be a good thing for security, and using the SQL Server user/role objects means that the user and role data wouldn't need to be stored in two places.

I did see some potentially relevant discussion at Best practice on users/roles on SQL Server for a web application, but I think this is a different question overall.

Thanks!

---

I recommend creating a sql login that the web application will use to connect to sql server. This way you are not impersonating any specific AD account which may get deleted, disabled in the future and can control the user strickly in SQL Server.

I would then recommend implementing roles based authentication in your application. This will enable you to create users and roles that are custom to your application and then assign users to them. This way if a user tries to access a resource that their role is not allowed it will not do any work. Here is a demo app based on this principle http://www.codeproject.com/KB/web-security/rolesbasedauthentication.aspx.