Stored procedure for Login

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

Can anyone share code to create a stored procedure for validating user login. I have two tables one is User(UserId(Pk),USername,Password,RoleId) and Roles(RoleId(PK),RoleName(Admin,User)).Username given as input wil be unique so how to check using stored procedure.

Posted 9-Aug-11 2:38am

CyborgForever

Add a Solution

Comments

Corporal Agarn 9-Aug-11 8:40am

What have you tried?

CyborgForever 9-Aug-11 8:43am

create Proc [dbo].[prcuserLogin]
( @Username VarChar(50), @Password varChar(50), @OutRes int OUTPUT )
AS
set
@OutRes = (SELECT count(*) FROM [dbo].Users WHERE Username = @Username And [Password] = @Password)
select case @OutRes
when 1 then 1 --Login is Correctelse0 --Bad login
else
0
end

_Zorro_ 9-Aug-11 8:52am

COUNT(*)? Not a good idea.
http://nirajrules.wordpress.com/2010/05/24/count-vs-countcolumnreference/

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

**R. Giskard Reventlov** · Answer 1 · 2011-08-09T02:45:00

Solution 1

One way might be like:

select
    count(*) as [Result]
from
    [User]
where
    UserName = @UserName

Returns 0 or 1: bit simplistic but it will tell you if the user is valid given the parameter. You probably need to do something more complex so as to return more information if the user is valid or a more meaningful return value if not; then you can use an exists type query to determine if the user is valid and then return more data.

if exists(select top 1 1 from [User] where UserName = @UserName)
begin
    -- do something.
end
else
begin
    -- do something else
end

Naturally, you'll need to scavenge what you need from this to make it work for you.

Posted 9-Aug-11 2:45am

R. Giskard Reventlov

Comments

_Zorro_ 9-Aug-11 8:52am

Didn't you forgot the password? And by the way, COUNT(*)? Not a good idea.

http://nirajrules.wordpress.com/2010/05/24/count-vs-countcolumnreference/

R. Giskard Reventlov 9-Aug-11 11:03am

Did you not read my reply? Did you miss the 'bit simplistic' and 'You probably need to do something more complex' and 'Naturally, you'll need to scavenge what you need from this to make it work for you.' These are never meant to be complete answers, rather, a guide or simple path to allow the OP to work it out for themselves.

_Zorro_ 10-Aug-11 4:16am

You are right, I'm sorry. I went a bit fast on that one. My bad.

_Zorro_ · Answer 2 · 2011-08-09T02:50:00

You have to be more specific...
Which login? Sql Server's? Your asp.net application?
What are you going to send to your stored procedure? If you're going to include the password, will it be sent in plain text? etc. etc. etc.

Read your question, and imagine you're some stranger trying to understand it.

Anyway, I suppose you're looking for something else, but here's what I can do with the provided information:

SQL

CREATE PROCEDURE [dbo].[CheckLogin]
@UserName NVARCHAR(128), @Password NVARCHAR(512), IsValid BIT OUTPUT

AS
BEGIN

SET @IsValid = SELECT COUNT(User.UserName) FROM [dbo].[User] WHERE User.UserName = @UserName AND User.UserPassword = @Password
		
END

Hope it helps

Sanju Choudhary S · Answer 3 · 2014-09-30T20:43:00

Store procedure for log in
CREATE PROCEDURE login1
(
@u varchar(50),
@p varchar(50)
)
AS
declare @up varchar (50)
select @up=password from login where uname=@u

if @up is null
return -1
else if @up=@p
return 1
else
return -2
----------------------------------------------------------------------------------
code on cs page
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

public partial class login : System.Web.UI.Page
{
SqlConnection con = new SqlConnection();

protected void Page_Load(object sender, EventArgs e)
{
con.ConnectionString = ConfigurationManager.ConnectionStrings["sp"].ConnectionString;
if (con.State == ConnectionState.Closed)
{
con.Open();
}
}

protected void Button3_Click(object sender, EventArgs e)
{
Int32 d;
d = checkuser(TextBox1.Text, TextBox2.Text);
if (d == -1)
{
Label1.Text = "wrong user";
}
if (d == -2)
{
Label1.Text = "wrong passwd";
}

if (d == 1)
{
Response.Redirect("Default.aspx");

}
}

private Int32 checkuser(string u, string p)
{
SqlCommand cmd = new SqlCommand("login1", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@u", SqlDbType.VarChar, 50).Value = u; //insert value in u
cmd.Parameters.Add("@p", SqlDbType.VarChar, 50).Value = p; //intert value in p
cmd.Parameters.Add("@ret", SqlDbType.Int);
cmd.Parameters["@ret"].Direction = ParameterDirection.ReturnValue;
cmd.ExecuteNonQuery();
Int32 K = Convert.ToInt32(cmd.Parameters["@ret"].Value);
cmd.Dispose();
return K;
}

}

Stored procedure for Login

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0