SQLTeam.com | Weblogs | Forums

Audit user login



I have a user table that contains username and password. iI would like to create a table to capture user logins successful. How should I go about it? Thanks.


How does the user login? If you have a web page where the login is handled, or similar in an APP, then I would do the "login", which is essentially just checking the Username and Password against the User Table, using a Stored Procedure.

You can then change that Stored Procedure in any way you want, in the future, without having to change your Web page / APP - as far as that is concerned it just executes:

MyLoginSProc @UserName='FRED01', @password='MySecretWord'

and checks if the result is, say, 0 or 1.

So the Stored Procedure could then be changed to log all login attempts in a UserLoginTable.

Beware that you should NOT store the password in plain text in your User Table, that would put you at high risk of being hacked and, if hacked, the hacker would then have all the users' passwords in plain text - which the users might well have, also, used on their Bank etc. accounts.

The normal way around that is to use a technique often referred to as SALT and HASH - I expect Google and Wikipedia have plenty to say on that ...