January 27, 2010 at 12:32 pm
After looking around this site and others, does this link demonstrate the best way to implement a way to save successful logins into a table? I am generating some reports that need a count of user logins by the hour.
http://www.sqlservercentral.com/articles/SQLServerCentral/sqlserver2005logontriggers/2366/
Has anyone implemented his script or something similar in a production SQL Server?
Steve
January 27, 2010 at 10:32 pm
I have implemented something very similar. Rather than using event notifications and queues, we just used a logon trigger that performed the insert statement as in the article.
The method described in the article should work very soundly.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 28, 2010 at 6:40 am
I just went to a session on Event Notifications by Jonathan Kehayias at SQLSaturday #32 in Tampa last Saturday where he showed how he did something similar so you should be all set if you follow the article.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 28, 2010 at 8:27 am
If you want to count the successful logins happend at particular time on your SQL Server the other way is to
1) extaract the sql server errorlog by filtering it on today's date
2) insert into a temptable
3) and running a query against it
caution: It is not recommended on the servers having large sized errorlog.
Cheers !!! 😉
January 28, 2010 at 8:31 am
I considered auditing successful logins, then reading the error log, but I prefer the other options which I think are easier in accomplishing what I want.
Only failed logins are being audited.
January 28, 2010 at 10:43 am
sgambale (1/28/2010)
I considered auditing successful logins, then reading the error log, but I prefer the other options which I think are easier in accomplishing what I want.Only failed logins are being audited.
I think it would be wise to audit both successful and failed logins. If you are implementing a solution for one, why not do both? There is much to be gained from also knowing who has been in the system along with which accounts have attempted to get into the system.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 28, 2010 at 12:09 pm
we are auditing failed logins, which go to the sql server error log.
I need successful logins to go to a table, so I can generate a report from it.
January 28, 2010 at 12:11 pm
Ok. Makes sense. Good luck.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 3, 2010 at 11:52 am
I implemented the event notification as described in http://www.sqlservercentral.com/articles/SQLServerCentral/sqlserver2005logontriggers/2366/
on a development database.
Referring to that link, suppose I want to limit the logins inserted into my table with a where clause in the stored procedure (where loginname in (...)).
Would doing this cause a buildup of events because I'm not inserting all of them?
I don't think so because the stored proc does the RECEIVE which gets the data from the queue, then the insert is done on some records (which meet my criteria in my where clause). For the records not inserted, I don't think those will pile up somewhere.
Anyone have a different opinion? I have to make sure I don't cause some sort of problem that will build up overt time when I put it on a production database.
February 3, 2010 at 12:19 pm
RECEIVE will remove the event from the queue so as long as you don't do a ROLLBACK TRANSACTION the processed event will be cleared from the queue.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply