Insert Statement

I am trying to complete an insert statement for user logins in order to keep
a record of all the times users login. Can someone help me with the statemen
t
below. Thanks very much for any help anyone can give me.
CREATE PROCEDURE [LoginValidation]
@.p_UserName char(12)
AS
SELECT UserName, Password, User_ID
FROM LoginDetails
WHERE UserName = @.p_UserName
--Want to insert a row in a LoginStats table to record user logins
GO
Here is something like the Insert statement which I require.
Can someone help me to complete this.
INSERT LoginStats
(
LoginID,
User_ID,
LoginTime
)
VALUES
(
How do I insert an automatic primary key,
how do i insert the User_Id from the previous query,
GETDATE()
)
Table : LoginStats
Column Name Data Type
(PK)LoginID char
(FK)User_ID char
LoginTime timestampHi
CREATE PROCEDURE [LoginValidation]
@.p_UserName char(12)
AS
SET NOCOUNT ON
INSERT LoginStats
(
LoginID,
User_ID,
LoginTime
)
SELECT
User_ID,
UserName,
GETDATE()
FROM LoginDetails
WHERE UserName = @.p_UserName
Regards
Mike
"Stephen" wrote:

> I am trying to complete an insert statement for user logins in order to ke
ep
> a record of all the times users login. Can someone help me with the statem
ent
> below. Thanks very much for any help anyone can give me.
> CREATE PROCEDURE [LoginValidation]
> @.p_UserName char(12)
> AS
> SELECT UserName, Password, User_ID
> FROM LoginDetails
> WHERE UserName = @.p_UserName
> --Want to insert a row in a LoginStats table to record user logins
> GO
> Here is something like the Insert statement which I require.
> Can someone help me to complete this.
> INSERT LoginStats
> (
> LoginID,
> User_ID,
> LoginTime
> )
> VALUES
> (
> How do I insert an automatic primary key,
> how do i insert the User_Id from the previous query,
> GETDATE()
> )
> Table : LoginStats
> Column Name Data Type
> (PK)LoginID char
> (FK)User_ID char
> LoginTime timestamp|||Stefen
If you change the LoginId to an Identity property run the following exampe
INSERT LoginStats
(
User_ID,
LoginTime
)
SELECT User_ID,GETDATE(),
FROM LoginDetails
WHERE UserName = @.p_UserName
AND NOT EXISTS
(
SELECT * FROM LoginStats WHERE LoginStats.User_ID=LoginDetails.User_ID
)
Or if LoginId is INTEGER run this one
INSERT LoginStats
(
LoginId
User_ID,
LoginTime
)
SELECT (SELECT COALESCE(MAX(LoginId),0)+1 FROM LoginStats ) ,
User_ID,GETDATE(),
FROM LoginDetails
WHERE UserName = @.p_UserName
AND NOT EXISTS
(
SELECT * FROM LoginStats WHERE LoginStats.User_ID=LoginDetails.User_ID
)
"Stephen" <Stephen@.discussions.microsoft.com> wrote in message
news:36B4384E-F8FC-48BD-8134-4585D8A7C6C0@.microsoft.com...
> I am trying to complete an insert statement for user logins in order to
keep
> a record of all the times users login. Can someone help me with the
statement
> below. Thanks very much for any help anyone can give me.
> CREATE PROCEDURE [LoginValidation]
> @.p_UserName char(12)
> AS
> SELECT UserName, Password, User_ID
> FROM LoginDetails
> WHERE UserName = @.p_UserName
> --Want to insert a row in a LoginStats table to record user logins
> GO
> Here is something like the Insert statement which I require.
> Can someone help me to complete this.
> INSERT LoginStats
> (
> LoginID,
> User_ID,
> LoginTime
> )
> VALUES
> (
> How do I insert an automatic primary key,
> how do i insert the User_Id from the previous query,
> GETDATE()
> )
> Table : LoginStats
> Column Name Data Type
> (PK)LoginID char
> (FK)User_ID char
> LoginTime timestamp|||What is "loginid" supposed to represent? It looks redundant to me. Your
pseudo- table structure drescribes LoginTime as a timestamp but I guess
you will want a DATETIME. The TIMESTAMP datatype has nothing to do with
recording date and time.
I guess you want something like this:
CREATE TABLE LoginStats (userid INTEGER NOT NULL REFERENCES Users
(userid), logintime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (userid,logintime))
SET @.userid =
(SELECT userid
FROM Users
WHERE username = @.p_username)
INSERT INTO LoginStats (userid)
SELECT @.userid
Since your SP presumably returns only one row you should find it more
efficient to return the result as output parameters rather than a
result set. Why are you returning the password as well? Don't store
passwords in the database - encrypted or otherwise - password recovery
mechanisms are inherent security flaws. Store only a secure hash of the
password and validate it in the middle tier. Finally, have you
considered using SQL Server's built in security and audit, which will
automatically record logins for you?
David Portas
SQL Server MVP
--|||You can create an IDENTITY column on your table to have an autoincrementing
column, that you could use as a primary key, but considering that the
combination of LoginTime and User_ID is unique already (unless your users
are really, really, really fast ;-)), you don't really need that.
So you can use the following:
CREATE TABLE LoginStats (
LoginTime DATETIME NOT NULL,
User_ID INT NOT NULL,
CONSTRAINT PK_LoginStats
PRIMARY KEY (LoginTime , User_ID),
CONSTRAINT FK_LoginStats__Users
FOREIGN KEY (User_ID)
REFERENCES Users (User_ID)
)
CREATE PROCEDURE [LoginValidation]
@.p_UserName char(12)
AS
SELECT UserName, Password, User_ID
FROM LoginDetails
WHERE UserName = @.p_UserName
INSERT INTO LoginStats (LoginTime, User_ID)
SELECT GETDATE(), User_ID
FROM LoginDetails
WHERE UserName = @.p_UserName
Jacco Schalkwijk
SQL Server MVP
"Stephen" <Stephen@.discussions.microsoft.com> wrote in message
news:36B4384E-F8FC-48BD-8134-4585D8A7C6C0@.microsoft.com...
>I am trying to complete an insert statement for user logins in order to
>keep
> a record of all the times users login. Can someone help me with the
> statement
> below. Thanks very much for any help anyone can give me.
> CREATE PROCEDURE [LoginValidation]
> @.p_UserName char(12)
> AS
> SELECT UserName, Password, User_ID
> FROM LoginDetails
> WHERE UserName = @.p_UserName
> --Want to insert a row in a LoginStats table to record user logins
> GO
> Here is something like the Insert statement which I require.
> Can someone help me to complete this.
> INSERT LoginStats
> (
> LoginID,
> User_ID,
> LoginTime
> )
> VALUES
> (
> How do I insert an automatic primary key,
> how do i insert the User_Id from the previous query,
> GETDATE()
> )
> Table : LoginStats
> Column Name Data Type
> (PK)LoginID char
> (FK)User_ID char
> LoginTime timestamp|||To be honest i was trying something simple but i understand that its probabl
y
not the best way for me to do things. In fact its possibly a bad way to do
things. I have my Password stored in a table in the DB and I check this
password in my code behind of my web application. I'm the only one with
access to the Database but i know its still not the most secure way. What
ways does SQL Server Built in security work. Do you have any example or link
s
which can help me do a simple database which stores user details and has
stored procedures for logins etc. I'm not sure of all the tables I should
have and what procedures I should write etc. Any help at all with this would
be very grateful.
In case you were wondering at the moment I basically have a datareader and i
check the reader and see if the password matches for the username entered. I
f
it does I allow the user to proceed otherwise a bring a message up saying
incorrect details. It works ok but I know its far from the best way of doing
it and i'm struggling to find better examples which aren't to difficult whic
h
I can use. Have you any suggestions. I did a google search on example
databases with login tables and stored procedures but didn't get much joy
unfortunately.
"David Portas" wrote:

> What is "loginid" supposed to represent? It looks redundant to me. Your
> pseudo- table structure drescribes LoginTime as a timestamp but I guess
> you will want a DATETIME. The TIMESTAMP datatype has nothing to do with
> recording date and time.
> I guess you want something like this:
> CREATE TABLE LoginStats (userid INTEGER NOT NULL REFERENCES Users
> (userid), logintime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
> PRIMARY KEY (userid,logintime))
> SET @.userid =
> (SELECT userid
> FROM Users
> WHERE username = @.p_username)
> INSERT INTO LoginStats (userid)
> SELECT @.userid
> Since your SP presumably returns only one row you should find it more
> efficient to return the result as output parameters rather than a
> result set. Why are you returning the password as well? Don't store
> passwords in the database - encrypted or otherwise - password recovery
> mechanisms are inherent security flaws. Store only a secure hash of the
> password and validate it in the middle tier. Finally, have you
> considered using SQL Server's built in security and audit, which will
> automatically record logins for you?
> --
> David Portas
> SQL Server MVP
> --
>|||If it's a public-facing web app where you can't otherwise authenticate users
then you will need to roll your own authentication. You can utilize the
cryptographic .NET classes to hash passwords for storing in the database and
the MSDN docs on these include examples of how to hash and salt passwords.
If it's an Intranet app then you may be able to use Windows or SQLServer
authentication, in which case you can take advantage of built in security an
d
turn on auditing of logins. To do this select the Security tab of the Server
Properties dialogue in EM and choose the required Audit Level option. Logins
will be recorded in the application log.
David Portas
SQL Server MVP
--