logon history

Question

Post reply

logon history

Shahin

Mr or Mrs. 500

Points: 538
More actions
July 21, 2016 at 7:16 am

#328029

Hi cuy's
How can I get an list for a user logon history to a Database in SQL 2008 R2?
I have to get logon history for a user for 3 different dates.
our user using Windows authentication to access the DB.
Thanks

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

There is no way to tell that without setting up an auditing mechanism upfront.

SQL Server does not store logon history anywhere, unless explicitly instructed to do so.

-- Gianluca Sartori

Shahin Mr or Mrs. 500 Points: 538 More actions · Answer 2

Thank you for the reply,

In fact I am sure the Database stors information on login and logout to the database, but question is how to get the info for that user.

Thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Shahin (7/21/2016)
In fact I am sure the Database stors information on login and logout to the database, but question is how to get the info for that user.

You are wrong on that point.

SQL Server does not keep historical logon/logoff information. Hence, unless you had some custom logging/auditing set up at the time, you cannot get that information for the users, it does not exist.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Shahin Mr or Mrs. 500 Points: 538 More actions · Answer 4

thak you again for quick update,

lets for a moment say SQL Auditing has been setup, what then? how can we get this information out of the DB?

Thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Depends what kind of auditing was set up and how.

If you mean the 'SQL Audit' feature specifically, then https://msdn.microsoft.com/en-us/library/cc280728.aspx, but that's not the only way that login monitoring could be set up. Other methods will have other ways of accessing the data, and none are retroactive.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Shahin Mr or Mrs. 500 Points: 538 More actions · Answer 6

Thank you,

sorry for my mistake posting the question here.

De datbase has a table that calls LoginRecords and there I can see when users login and logout.

So I think I should be able to run a query to extract data what I need right? so I want to get the login and logout for the user X on 01/05/2016 and 05/06/2015 how can I get it out of this table?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Shahin (7/21/2016)
De datbase has a table that calls LoginRecords and there I can see when users login and logout.
So I think I should be able to run a query to extract data what I need right? so I want to get the login and logout for the user X on 01/05/2016 and 05/06/2015 how can I get it out of this table?

No idea.

That's something custom that your application does. Ask your developers.

That table is NOT something that SQL populates, it's not something that any instance other than yours has.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

HappyGeek SSCoach Points: 18729 More actions · Answer 8

GilaMonster (7/21/2016)
Shahin (7/21/2016)
De datbase has a table that calls LoginRecords and there I can see when users login and logout.
So I think I should be able to run a query to extract data what I need right? so I want to get the login and logout for the user X on 01/05/2016 and 05/06/2015 how can I get it out of this table?
No idea.
That's something custom that your application does. Ask your developers.
That table is NOT something that SQL populates, it's not something that any instance other than yours has.

Gail is right, this is an application entry, probably as part of an authentication process via a stored procedure. You are able to query the table, but without knowing the structure or having any example data it is difficult for anyone to advise.

...

Shahin Mr or Mrs. 500 Points: 538 More actions · Answer 9

Thank you for your reply,

if I run:

SELECT Top 1000 [LoginRecordID]

,[LoginTS]

,[LogoutTS]

,[LoginID]

FROM [myDB].[dbo].[LoginRecords]

where LoginID = 'user1'

it returens the beging of the table, how can I ask for the result between 03/01/2016 and 08/012016

Thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

Again, no idea because we don't know the definition of the table or what the data in it looks like. Whatever you have there is custom, not something that's in every SQL Server instance.

As a wild guess, filter on one of the TS columns (assuming they stand for timestamp)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass