This is a general scenario. And ASP.NET has built-in solution for that, called role based authentication. To use that, you need to use one of the authentication methods
supplied with asp.net[
^]: form based or window based (you could make your own, but that would be complicated). By default the windows based is using the windows identity as authentication basis, and windows group memberships as role memberships. The form based has more options, but as you tagged your question with sql server too, I suppose you want to store your users and role information in the database. Well, that is also ready-to-use. Here you have two comprehensive articles:
http://chrisfulstow.com/installing-asp-net-membership-roles-and-profiles-support-in-sql-server[
^] and
http://www.asp.net/web-forms/tutorials/security/roles/role-based-authorization-cs[
^]
Of course, you can extend these features, replace providers and so on - but these are advanced tasks, so I suggest you start with the built-in ones.