Does MS SQLServer support auditing in the way that you can for Sybase?

[byrocat]

I know that there are tools like Lumigent, but an wondering about the
internal facilities to track events such as table creation, security
operations (add login, add role), and such.

Under Sybase, there is a set of procedures that permit you to set
theses events and to record the results for later extraction and
analysis.

The Profiler seems to have a lot of the same functionality but this
appears to be more along the lines of running a monitor.

Can the events be tracked without Profiler running?

Can the events being tracked be recorded in the system of SQLServer
logs?

[Erland Sommarskog]

Re: Does MS SQLServer support auditing in the way that you can for Sybase?

byrocat (bdealhoy@sympa tico.ca) writes:[color=blue]
> I know that there are tools like Lumigent, but an wondering about the
> internal facilities to track events such as table creation, security
> operations (add login, add role), and such.
>
> Under Sybase, there is a set of procedures that permit you to set
> theses events and to record the results for later extraction and
> analysis.
>
> The Profiler seems to have a lot of the same functionality but this
> appears to be more along the lines of running a monitor.
>
> Can the events be tracked without Profiler running?[/color]

Yes. But I am not going to say that it is particular appealing.

The good news is that in SQL Server 2005, currently in beta, you can
define triggers on database and server level, which makes this sort of
auditing a lot more reasonable to do.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[byrocat]

Re: Does MS SQLServer support auditing in the way that you can for Sybase?

Erland, would you haoppen to have an URL to a Technote or soemthing
similar that lays out the procedures for setting auditing of events
other than logins?

[Erland Sommarskog]

Re: Does MS SQLServer support auditing in the way that you can for Sybase?

byrocat (bdealhoy@sympa tico.ca) writes:[color=blue]
> Erland, would you haoppen to have an URL to a Technote or soemthing
> similar that lays out the procedures for setting auditing of events
> other than logins?[/color]

You mean with Profiler? No, I don't have any reference any such texts.

You can look up C2 auditing in Books Online, but you will not find much.
There is a reference to a "C2 Administrator's and User's Security Guide",
but there is no link.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Simon Hayes]

Re: Does MS SQLServer support auditing in the way that you can for Sybase?


"byrocat" <bdealhoy@sympa tico.ca> wrote in message
news:1123616848 .996208.232970@ o13g2000cwo.goo glegroups.com.. .[color=blue]
> Erland, would you haoppen to have an URL to a Technote or soemthing
> similar that lays out the procedures for setting auditing of events
> other than logins?
>[/color]

See sp_trace_create (and related procs) in Books Online. I agree with Erland
that it's not always perfect, but you can trace anything that Profiler can
trace, which obviously extends beyond just login events.

Simon