Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

Who has accessed my 2005 server? Expand / Collapse
Author
Message
Posted Thursday, April 9, 2009 7:02 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, June 23, 2014 2:21 AM
Points: 1,205, Visits: 921
Great script Jack! Thanks for this and I know its been out for a while already but I have been real busy. This will help me a lot. I will definitely implement it. I would also like to get a list of login failures so I can follow it up because someone one day told me that if you have a lot of login failures it may be someone trying to hack the database. Be glad if you could help me!

Manie Verster
Developer
Johannesburg
South Africa

I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
Post #693950
Posted Thursday, April 9, 2009 10:10 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:44 AM
Points: 11,149, Visits: 12,891
Thanks Manie.

You can use the default trace to check for login failures, here's an idea on how:

SELECT
I.*
FROM
sys.traces T CROSS Apply
:: fn_trace_gettable(CASE WHEN CHARINDEX('_', T.[path]) <> 0
THEN SUBSTRING(T.PATH, 1,
CHARINDEX('_', T.[path]) - 1) +
'.trc'
ELSE T.[path]
End, T.max_files) I JOIN
sys.trace_events AS TE ON
I.EventClass = TE.trace_event_id
WHERE
TE.[name] = 'Audit Login Failed'

At R. Barry Young's suggestion I am working on a presentation about mining the default trace that will likely turn into at least one article here at SSC within a year or so. Your question helps with ideas. It takes me awhile to write and I have a series on Profiler that I need to finish first.




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #694168
Posted Friday, April 10, 2009 6:31 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, June 23, 2014 2:21 AM
Points: 1,205, Visits: 921
Thanks Jack, this is real nice and I have already added it to my collection of scripts. I look forward to that articles about the Profiler!

Manie Verster
Developer
Johannesburg
South Africa

I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
Post #694771
Posted Monday, April 13, 2009 3:53 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 9:59 AM
Points: 886, Visits: 1,544
Jack Corbett (2/9/2009)
I modified the code in the script to fix this oversight. Steve just needs to release the updated code. Here it is here as well:

SELECT 
I.NTUserName,
.....
{Removed for brevity}

... I.LoginSid is not null
Group By
I.NTUserName,
I.loginname,
I.SessionLoginName,
I.databasename,
S.principal_id,
S.sid,
S.type_desc,
S.name

I overlooked the fact that the Path stored in sys.traces includes the numeric extension to the file name. The CASE strips the numeric portion of the file name.


Apolagies if this sounds dumb because I may just be missing something but the way I read your reply with this new code was that it would load all of the traace files and not just the last one else why strip out the file number portion of the trace file name? When I run this exact code as is I only get data from the most recent trace file.

Whats weird about this is I swear there was a forum posting discussing this same thing with the fn_Trace_Gettable() function/procedure and I thought someone did list a way to get the thing to load content from all trc files in that same directory.

Am I going crazy or is that familiar to anyone else, that there is a way to load all the trc file?

Thanks Jack!


Kindest Regards,

Just say No to Facebook!
Post #696185
Posted Tuesday, April 14, 2009 2:28 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:44 AM
Points: 11,149, Visits: 12,891
The "new" code with the file number removed should load all the files for the specified trace. On my laptop, which I reboot somewhat regularly, and does not have a really active SQL Server, the code without removing the file number only queries the "active" file while the new code returns data from all the files.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #696361
Posted Tuesday, April 21, 2009 11:27 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, September 18, 2011 7:42 PM
Points: 1, Visits: 35
Didn't work for me. The following error when attempting to run this on 9.0.3159 in a clustered environment.

Msg 156, Level 15, State 1, Line 14
Incorrect syntax near the keyword 'CASE'.


Post #702053
Posted Wednesday, April 22, 2009 6:03 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:44 AM
Points: 11,149, Visits: 12,891
krombit,

I have seen this error. I get this error when I run the script in a database where the compatibility level is set to something other than SQL Server 2005. So check the compatibility level of the db you are running the query in or run the query in tempdb.




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #702257
Posted Thursday, April 30, 2009 3:59 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, June 27, 2014 8:02 AM
Points: 3,241, Visits: 4,996
Very Useful script. Thanks...

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sometimes, winning is not an issue but trying.

You can check my BLOG here

Post #707513
Posted Tuesday, July 7, 2009 8:32 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, October 28, 2013 8:50 AM
Points: 1,607, Visits: 1,039
Hi

Is there any similar script that can be used in SQl server 2000.



Tanx
Post #748979
Posted Tuesday, July 7, 2009 9:13 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:44 AM
Points: 11,149, Visits: 12,891
Eswin,

There is not a similar script for 2000 because 2000 does not have the default trace. You can create a trace in 2000 that mimics the 2005 Default Trace and then you could create a similar script that reads that trace file(s). You would need to put the Trace creation in a stored procedure and then have that sp run at startup.




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #748998
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse