Check mail and password with sp

How can I write a stored procedure with SQL 2000 Server to check an e-mail and password entered by the user, and if both exist select the Id field ?

Table Users:
UserId
First_name
Surname
Age
E-mail
Password

If it is possible I would like to know how can I write the error messages in three cases: If E-mail doesn't exist, if password doesnt exist and if both doesnt exist. And I would like that those messages would be able to appear on the website application.

ThanksMay check Planet SC (http://planet-source-code.com) for any CE.|||Ok thank you

Check login

Hello, everyone. I am trying to create a procedure that checks a user's login, in the database. The login is based on e-mail address and password.

How do I construct the stored procedure to check if the username and password are correct?

Thanks,

Antonio

You could either use a stored procedure, or a function.

Code Snippet

CREATE PROCEDURE dbo.VerifyLogin

( @.eMail varchar(200),

@.Pwd varchar(20)
)

AS

SELECT isnull(( SELECT DISTINCT 'Verified'
FROM MyUsersTable

WHERE ( eMail = @.eMail
AND Pwd = @.Pwd
)
), 'BOGUS' )
GO

(or)

CREATE FUNCTION dbo.fnVerifyLogin

( @.eMail varchar(200),

@.Pwd varchar(20)
)
RETURNS varchar(10)
AS

SELECT isnull(( SELECT DISTINCT 'Verified'
FROM MyUsersTable

WHERE ( eMail = @.eMail
AND Pwd = @.Pwd
)
), 'BOGUS' )
GO

|||

Just a couple of points to make about password storage in databases.

You should never store a password in a clear (unencrypted) form in a database. Preferably you should not even store it in a decryptable form, but rather a value managled by a one-way hash. SQL Server 2005 now has the HashBytes function which will perform a cryptographically valid hash operation.

It is also recommended that you not perform a straightforward hashing but rather salt the value before hashing with another value. This means adding another known piece of information to the value before hashing the combination. This has a 2-fold advantage:

It makes a brute force attack of the hash algorithm with all the possible values harder (basically impossible if the salt is different for each password). Be aware there are projects on the internet where people are building hash lists for all the characters strings up to a certain length composed of a standard set of characters (mainly alpha/digit - upper case only) for some standard hash algorithms. Good reason for using longer passwords (12+ characters) and at least one unusual character. If you use a different salt for each user (I normally use something based upon the primary ID in the User Table) then it prevents the insider attack of moving the password hash from a user whose password is known to another allowing access to the system as that user (restoring the old hash removes signs of the hack). If the salt is held as a field in the table they can move that too (though a unique constraint might make that more difficult).sql

Chaning password

I have several merge replications running pushing subscriptions from one
server to the other. After some problems we recreated the databases (se
previous questions). However after pushing up all data everything worked fine
until we changed the administrator password on the subsciber server. Now I
get an error message 20084 at the publisher /18452 at the subscriber and the
message "Login failed for user '(null)'. Reason: Not associated with a
trusted SQL Server connection." What is the problem? i have registred the
servers via the 'sa' user and connect via the 'sa' user and have both
sql-server and windows as login possibilities. Sql-server and agent is
started via the administrator account (win2000) but i have stopped and
restarted the service changing the password in the services.
Best wishes
Mats
The "Login failed for user '(null)'. Reason: Not associated with a trusted
SQL Server connection." error normally means one of three things.
The NT account you are using to authenticate with does not have rights to
access the machine, its password is invalid, or you are using windows
authentication only and trying to connect using a SQL account and password.
You have to be a little careful with the account on the subscriber.
When the subscriber's SQL Server agent connects to the publisher it by
default will impersonate the SQL Server agent account on the publisher. In
other words the accounts and passwords of the account the SQL Server agent
on the Subscriber will have to match the account on the Publisher, or will
have to be in the administrator group on the publisher.
You can also specify a SQL account. In this case your SQL Server agent
account on the subscriber will have to match the SQL Server agent account on
the publisher, or be in the administrator group, or have rights to access
the snapshot folder share on the publisher.
I think your best approach, if you can do it, is to make the administrator
accounts on both servers have the same password, or to use another
administrator account on the subscriber, and add this account to the admin
group on the publisher.
HTH - its a little tricky to implement.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Mats" <Mats@.discussions.microsoft.com> wrote in message
news:E2956017-73FA-4EBA-8D86-F58A0B4D24C7@.microsoft.com...
>I have several merge replications running pushing subscriptions from one
> server to the other. After some problems we recreated the databases (se
> previous questions). However after pushing up all data everything worked
> fine
> until we changed the administrator password on the subsciber server. Now I
> get an error message 20084 at the publisher /18452 at the subscriber and
> the
> message "Login failed for user '(null)'. Reason: Not associated with a
> trusted SQL Server connection." What is the problem? i have registred the
> servers via the 'sa' user and connect via the 'sa' user and have both
> sql-server and windows as login possibilities. Sql-server and agent is
> started via the administrator account (win2000) but i have stopped and
> restarted the service changing the password in the services.
>
> --
> Best wishes
> Mats