store procedure example

Check this
http://forums.asp.net/t/1453256.aspx/1[^]

http://stackoverflow.com/questions/548755/problem-with-login-stored-procedure-coding[^]

verifying username and password by calling stored procedure from c#.net[^]

http://www.dotnetspider.com/forum/56167-How-write-stored-procedure-login-page.aspx[^]

As far as I understood your question you want stored procedure to verify username and password

Refer this:

http://forums.asp.net/t/1453256.aspx/1[^]

In addition to other solutions remember to store your passwords encrypted, Its not necessary but it will bring you more security.
Here is a simple example of users table :

CREATE TABLE [dbo].[User](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [UserName] [varchar](50) NOT NULL,
    [Pass] [image] NULL,
    [FirstName] [varchar](50) NULL,
    [LastName] [varchar](50) NULL,
 CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

And the login procedure :

CREATE PROCEDURE sp_LoginUser
@uname as varchar(100),
@password as varchar(100)
AS
SELECT     *
FROM         [User] 
WHERE		(UserName=@uname ) and (convert(varbinary, Pass) = HASHBYTES('MD5', @password))

Check the returned rows after calling this procedure if there is no result so login is failed.
Also use HASHBYTES when inserting new users or editing their passwords.

To get the HASHBYTES output in your .NET code use this snippet :

public static byte[] HashString(string input)
{
    System.Security.Cryptography.MD5CryptoServiceProvider csp = new System.Security.Cryptography.MD5CryptoServiceProvider();
    return csp.ComputeHash(System.Text.Encoding.ASCII.GetBytes(input));      
}

Hope it helps.