Microsoft SQL Server forum - Auditing user and security related activities in SQLServer

Author

Auditing user and security related activities in SQLServer

byrocat

2005-07-21, 1:23 pm

On the other database types, there is an audit capability in that you
record such items as
failed login attempts
attemtped access to tables user is not authroized to
changes to databse schema
changes to permissions
changes to logins (add, delete, lock, unlock, passwrod reset)

All I can find in the SQLServer documentation is the reference to
tracking failed logins when you set up a database, plus the Profiler's
activities.

Yes, I'm taking voer my first SQLServer database and have been asked to
make sure that this database is closely monitored for inappripriate
activity.

Questions:
1) Does SQLServer have this capability? (Sybase has this, which is
where I'm coming from)
2) Does SQLServer do this automatically or do I have to set up the
events to be tracked as happens with Sybase?
3) What commands are there for setting up these events to be tracked?

Thanks in advance!

Erland Sommarskog

2005-07-21, 8:24 pm

byrocat (bdealhoy@sympatico.ca) writes:
> On the other database types, there is an audit capability in that you
> record such items as
> failed login attempts
> attemtped access to tables user is not authroized to
> changes to databse schema
> changes to permissions
> changes to logins (add, delete, lock, unlock, passwrod reset)

In SQL 2000 a Profiler trace is the only option, save for failed logins
which can be tracked by other means.

In SQL 2005 you can set up server and database triggers to audit the
last three events. SQL 2005 is currently in beta.

Attempt to access tables for which user does not have permission is
not something you can track well even in SQL 2005. In fact if a user
says "SELECT * FROM tbl_I_may_not_see" he gets as the same error message
as if it did not exist at all.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Oscar Santiesteban Jr.

2005-07-21, 8:24 pm

Take a look at www.lumigent.com and their product called Entegra.
Depening on how much you value your time or how much the company your work
for is willing to spend, this may be a "no-brainer" decision. It does most
of what you want to do.
It is priced per processor, ie 1-2 cpu or 3-4 cpu. You can even monitor
"select" statements.

Oscar

"byrocat" <bdealhoy@sympatico.ca> wrote in message
news:1121968677.798082.274370@g14g2000cwa.googlegroups.com...
> On the other database types, there is an audit capability in that you
> record such items as
> failed login attempts
> attemtped access to tables user is not authroized to
> changes to databse schema
> changes to permissions
> changes to logins (add, delete, lock, unlock, passwrod reset)
>
> All I can find in the SQLServer documentation is the reference to
> tracking failed logins when you set up a database, plus the Profiler's
> activities.
>
> Yes, I'm taking voer my first SQLServer database and have been asked to
> make sure that this database is closely monitored for inappripriate
> activity.
>
> Questions:
> 1) Does SQLServer have this capability? (Sybase has this, which is
> where I'm coming from)
> 2) Does SQLServer do this automatically or do I have to set up the
> events to be tracked as happens with Sybase?
> 3) What commands are there for setting up these events to be tracked?
>
> Thanks in advance!
>

Sponsored Links