SQL Server 2000 auditing

[ceil32]

I have SQL Server 2000 installed on a Windows 2000 Server.

I want to setup auditing for the following if possible:

Failed logon attempts
Account creation & updates
Privileged activity
Changes to key tables

I ran the following command from a query line:

OSQL -S <servername> -E and then restarted the SQL services

I'm now getting between 5-10 .trc files per day, each about 205Mb in size.

Can anyone help me narrow the auditing down so the trace files aren't so big??

Thanks..

 

[mutley1]

Hi Ceil,

1. Failed logons - in enterprise manager, right click on the server, properties and then security and check the radio button in "audit Level" that says failure. This will write to the SQL Logs when a failed login attempt occurs.
2, 3, and 4 - as far as I know, there is nothiing to audit this in 2000, but there are DDL triggers in 2005 that would monitor this.

I believe this is right but am sure one of the experts will correct me if I am off the mark.

Rgds,

M.

 

[mrdenny]

There isn't a whole lot of auditing available in SQL Server 2000. Failed and successful logins is all that you can do natively.

Setting up a trace file is about all you can do to monitor the statements against the database.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2008 Implementation and Maintenance / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Admin (SQL 2005/2008) / Database Dev (SQL 2005)

My Blog

 

[ceil32]

Thanks Guys - how do I turn off auditing now?

Given that I ran 'osql -S Jordan - E'

 

[mutley1]

That just looks like a connection string.......-S being server, -E being trusted connection. Unless you had anything else in there that would just connect you to the server (provided your login had access for the trusted connection).

Did you have anything else in the OSQL string?