how to apply role based security in 3 tier architecture

stored procedure for login
ALTER procedure login3
@usrnam varchar(50),@usrpwd varchar(50)
as
declare @p int
set @p=1
if exists (select * from tbusr where usrnam=@usrnam and usrpwd=@usrpwd)
begin
return @p
end
else
return

App Code class is

 public abstract class clscon
    {
        protected SqlConnection con = new SqlConnection();
        public clscon()
        {
            con.ConnectionString = ConfigurationManager.ConnectionStrings["cn"].ConnectionString;
        }
    }
public class clslogin : clscon // for login
    {
public Int32 save1(clsusrprp p) // to check validity of user being logged in 
        {
            if (con.State == ConnectionState.Closed)
            {
                con.Open();
            }
            SqlCommand cmd = new SqlCommand("login3", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@usrnam", SqlDbType.VarChar, 50).Value = p.p_usrnam;
            cmd.Parameters.Add("@usrpwd", SqlDbType.VarChar, 50).Value = p.p_usrpwd;
            SqlParameter p1 = new SqlParameter("@ret", SqlDbType.Int);
            p1.Direction = ParameterDirection.ReturnValue;
            cmd.Parameters.Add(p1);
            //cmd.Parameters.Add("@usrregid", SqlDbType.Int).Value = p.p_usrregid;
            cmd.ExecuteNonQuery();
            Int32 k = Convert.ToInt32(cmd.Parameters["@ret"].Value);
            cmd.Dispose();
            return k;
        }
}

Code file is

 public partial class login : System.Web.UI.Page
{
    nshospital.clsusrprp objprp = new nshospital.clsusrprp();
    nshospital.clslogin obj = new nshospital.clslogin();
    SqlConnection con = new SqlConnection();
    protected void Page_Load(object sender, EventArgs e)
    {
        if (Page.IsPostBack == false)
        {
            con.ConnectionString = ConfigurationManager.ConnectionStrings["cn"].ConnectionString;
            if (con.State == ConnectionState.Closed)
            {
                con.Open();
            }
        }
    }
    protected void Button2_Click(object sender, EventArgs e)
    {
        objprp.p_usrnam = TextBox3.Text;
        objprp.p_usrpwd = TextBox4.Text;
        Int32 k = Convert.ToInt32(obj.save1(objprp));
if (k == 1) // valid user
        {
            string a = obj.dispname(TextBox3.Text);
            Session["ss"] = a;
            Session.Timeout = 1;
Response.Redirect("Default52.aspx");
        }
        else
        {
            Label6.Text = "invalid user name or password";
            LinkButton1.Visible = true;
        }
    }

 table is
 
usrid	int	Unchecked
usrnam	varchar(50)	Checked
usrpwd	varchar(50)	Checked
usrregid	int	Checked
usrrol	varchar(50)	Checked
usrhque	varchar(50)	Checked
usrhans	varchar(50)	Checked
		Unchecked