MVC3 Multiple Users/Connection Strings

I want to make sure that I’m on the right track with this one.

I have a site which has two backend areas, one for school and one for admin. I would like to authenticate the areas so that the school area has less DB privileges than admin. To do this my idea is to have two logins in Sql Server Management Studio School and Admin, which I can map certain roles to. Maybe the school section will have only read access and the admin read and write etc..

Please can somebody advise me as to the best way to go about implementing this. Do I need multiple connection strings (one for admin and one for school)? Can this be done using Form Authentication?

I am currently connecting to an existing database that I have created using Sql Server Management Studio and already have a means of logging in which will set the FormsAuthentication and this works well in the sense that I can add the authorize attribute to my school backend controller which stops the pages that require a logged in school from being displayed unless a school is logged in. The question is really how would I make this more specific so that only a school login is allowed to see this area and not an admin member who has logged in as they would also have set the FormsAuthentication.

I have done a lot of googling in but have not found anything specific to my problem, hence this post.

I can produce code if needed and am not asking for somebody to write it for me but a theoretical explanation of how to solve this kind of security model.

Thanks in advance for your help.

Working Solution using a Custom Role Provider

Account Controller code (Now one for both Admin and School)

[HttpPost]
public ActionResult LogOn(LogOn model, string returnUrl)
{
    if (ModelState.IsValid)
    {
        if (Membership.ValidateUser(model.UserName, model.Password))
        {
            FormsAuthentication.SetAuthCookie(model.UserName, model.RememberMe);
            if (Url.IsLocalUrl(returnUrl) && returnUrl.Length > 1 && returnUrl.StartsWith("/")
                && !returnUrl.StartsWith("//") && !returnUrl.StartsWith("/\\"))
            {
                return Redirect(returnUrl);
            }
            else
            {
                // Now it's our job to route the user to the correct place. Ask our DB Helper to tell 
                // us what kind of user they are and route accordingly.
                string sPage = "Index";
                string sController = "Home";

                var Role = DBHelperFunctions.Instance().GetRoleForUser(model.UserName);

                switch (Role.role_name)
                {
                    case LanguageSchoolsConstants.m_RoleAdministrator:
                        {
                            sController = "AuthorisedAdmin";
                        }
                        break;
                    case LanguageSchoolsConstants.m_RoleSchool:
                        {
                            sController = "AuthorisedSchool";
                        }
                        break;
                }

                return RedirectToAction(sPage, sController);
            }
        }
        else
        {
            ModelState.AddModelError("", "The user name or password provided is incorrect.");
        }
    }

DB Helper Function used in above method:

public role GetRoleForUser(string sUserName)
{
    // Should only ever have one role...
    role Role = (from roles in DBModel.roles
                 join userrole in DBModel.user_role on roles.role_id equals userrole.role_id
                 join users in DBModel.users on userrole.user_id equals users.user_id
                 where users.username == sUserName
                 select roles).FirstOrDefault();

    return Role;
}

Web.config changes to allow role providor to be invoked:

<roleManager defaultProvider="RoleProvider" enabled="true" cacheRolesInCookie="true">
      <providers>
        <clear />
        <add name="RoleProvider" type="namespace.Models.Security.CustomRoleProvider" />
        <add name="AspNetSqlRoleProvider" type="System.Web.Security.SqlRoleProvider" connectionStringName="Entities" applicationName="/" />
        <add name="AspNetWindowsTokenRoleProvider" type="System.Web.Security.WindowsTokenRoleProvider" applicationName="/" />
      </providers>
    </roleManager>

My Role Provider

 using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Security;

namespace neamsepace.Models.Security
{
    public class CustomRoleProvider : RoleProvider
    {
        public override void AddUsersToRoles(string[] usernames, string[] roleNames)
        {
            throw new NotImplementedException();
        }

        public override string ApplicationName
        {
            get
            {
                throw new NotImplementedException();
            }
            set
            {
                throw new NotImplementedException();
            }
        }

        public override void CreateRole(string roleName)
        {
            throw new NotImplementedException();
        }

        public override bool DeleteRole(string roleName, bool throwOnPopulatedRole)
        {
            throw new NotImplementedException();
        }

        public override string[] FindUsersInRole(string roleName, string usernameToMatch)
        {
            throw new NotImplementedException();
        }

        public override string[] GetAllRoles()
        {
            throw new NotImplementedException();
        }

        public override string[] GetRolesForUser(string username)
        {
            using (DB db = new DB())
            {
                string[] RolesForUser = null;

                user User = db.users.FirstOrDefault(u => u.username.Equals(username, StringComparison.CurrentCultureIgnoreCase) ||
                                                         u.email.Equals(username, StringComparison.CurrentCultureIgnoreCase));

                var roles = from dbroles in db.roles
                            join userroles in db.user_role on dbroles.role_id equals userroles.role_id
                            join users in db.users on userroles.user_id equals users.user_id
                            where users.user_id == User.user_id
                            select dbroles.role_name;

                if (roles != null)
                {
                    RolesForUser = roles.ToArray();
                }
                else
                {
                    RolesForUser = new string[] { };
                }

                return RolesForUser;

            }
        }

        public override string[] GetUsersInRole(string roleName)
        {
            throw new NotImplementedException();
        }

        public override bool IsUserInRole(string username, string roleName)
        {
            using (DB db = new DB())
            {
                bool bUserInRole = false;

                user User = db.users.FirstOrDefault(u => u.username.Equals(username, StringComparison.CurrentCultureIgnoreCase) || 
                                                         u.email.Equals(username, StringComparison.CurrentCultureIgnoreCase));

                var roles = from dbroles in db.roles
                            join userroles in db.user_role on dbroles.role_id equals userroles.role_id
                            join users in db.users on userroles.user_id equals users.user_id
                            where users.user_id == User.user_id
                            select dbroles.role_name;

                if (User != null)
                {
                    bUserInRole = roles.Any(r => r.Equals(roleName, StringComparison.CurrentCultureIgnoreCase));
                }

                return bUserInRole;
            }
        }

        public override void RemoveUsersFromRoles(string[] usernames, string[] roleNames)
        {
            throw new NotImplementedException();
        }

        public override bool RoleExists(string roleName)
        {
            throw new NotImplementedException();
        }
    }
}

Controller that uses Authorize.

[Authorize(Roles = LanguageSchoolsConstants.m_RoleAdministrator)]
public class AuthorisedAdminController : Controller
{
    //
    // GET: /AuthorisedAdmin/

    public ActionResult Index()
    {
        return View();
    }

}

I really hope that this helps anybody please feel free to comment!

Thanks for all your help.