Logins Successful or Failed Auditing

Hello,
I have a task to do.
I need to get all the logins (successful or failed) into a table along with
the timestamp which I can keep truncating as and when I want so that it
doesn't grow too big.
I have tried using server side traces to do this. But this has 2
disadvantages which make me NOT want to use it.
1. The output file (.trc file) can be viewed only when the rollover size is
meet or when the SQL Server is stopped <--bad mojo.
2. There is no solution for when the output files keep on growing and
rolling over. I have 75+ servers to monitor. I want something that will keep
record for say, last 1 week thats all. Output in a table can be so so better.
Apparently, using server side trace you cant have output in table.
Does anyone have any suggestions ? I figured I could do something like put a
trigger on sysprocesses but this won't give me the failed logins.
Also, server load would be incredible with a trigger shooting off each time.
Any help is appreciated. Oh, and C2 Audit option is out of the question.
Thats just too detailed.
Thanks.
Regards,
Kunal
Hello,
The only thing I can suggest is that you can import the data from the .trc
file into a table using the function fn_trace_gettable (see BOL for exact
syntax).
Hope this helps.
"kunalap" wrote:

> Hello,
> I have a task to do.
> I need to get all the logins (successful or failed) into a table along with
> the timestamp which I can keep truncating as and when I want so that it
> doesn't grow too big.
> I have tried using server side traces to do this. But this has 2
> disadvantages which make me NOT want to use it.
> 1. The output file (.trc file) can be viewed only when the rollover size is
> meet or when the SQL Server is stopped <--bad mojo.
> 2. There is no solution for when the output files keep on growing and
> rolling over. I have 75+ servers to monitor. I want something that will keep
> record for say, last 1 week thats all. Output in a table can be so so better.
> Apparently, using server side trace you cant have output in table.
> Does anyone have any suggestions ? I figured I could do something like put a
> trigger on sysprocesses but this won't give me the failed logins.
> Also, server load would be incredible with a trigger shooting off each time.
> Any help is appreciated. Oh, and C2 Audit option is out of the question.
> Thats just too detailed.
> Thanks.
> Regards,
> Kunal
|||How about writing a small VB.NET app that is scheduled frequently and reds off of the event log and
import into a table?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"kunalap" <kunalap@.discussions.microsoft.com> wrote in message
news:DE02C02D-0C1D-447B-B98C-BE4F63F5A207@.microsoft.com...
> Hello,
> I have a task to do.
> I need to get all the logins (successful or failed) into a table along with
> the timestamp which I can keep truncating as and when I want so that it
> doesn't grow too big.
> I have tried using server side traces to do this. But this has 2
> disadvantages which make me NOT want to use it.
> 1. The output file (.trc file) can be viewed only when the rollover size is
> meet or when the SQL Server is stopped <--bad mojo.
> 2. There is no solution for when the output files keep on growing and
> rolling over. I have 75+ servers to monitor. I want something that will keep
> record for say, last 1 week thats all. Output in a table can be so so better.
> Apparently, using server side trace you cant have output in table.
> Does anyone have any suggestions ? I figured I could do something like put a
> trigger on sysprocesses but this won't give me the failed logins.
> Also, server load would be incredible with a trigger shooting off each time.
> Any help is appreciated. Oh, and C2 Audit option is out of the question.
> Thats just too detailed.
> Thanks.
> Regards,
> Kunal
|||Thanks for the reply Anoop.
I was already aware of the function. But it is only for viewing the data in
query analyzer.
I guess I will have to setup another job to delete the older .trc files.
Thanks.
-Kunal.
"Anoop" wrote:
[vbcol=seagreen]
> Hello,
> The only thing I can suggest is that you can import the data from the .trc
> file into a table using the function fn_trace_gettable (see BOL for exact
> syntax).
> Hope this helps.
>
> "kunalap" wrote: