SQL Server Auditing

[sixpot]

Hi Folks

Apologies if this is a stupid question but I've only recently taken on two SQL Server environments at my company so am a newbie to the product, although I've been a Sybase DBA for several years. Almost immediately we get audited and they're asking questions like "how can you tell who's logged in and when?" and "where are the logs of data changes?". There's nothing currently in place to do this so I'm looking desperately through manuals for a SQL Server equivilent of Sybase's 'sybsecurity' but can't find anything. Is this right?? I've found the SQL Server Logs but these just seem to record spid numbers against events. Is there anything else I can use to actually tie down login times, etc, to individual users?

Many thanks!

Luke

 

[jester777]

You can change the audit level of SQL server to log details of login times etc. In SQL Enterprise Manager, right click your server and select PROPERTIES. On the security tab you will see Audit Level, if you select All you will get tthe following info (taken from books online)

All

Audit on both successful and failed login attempts. You can record attempted user accesses as well as other SQL Server log information, and enable auditing for both security modes, and you can record information on both trusted and nontrusted connections. Log records for these events appear in the Windows application log, the SQL Server error log, or both, depending on how you configure logging for your SQL Server.
If you select this option, you must stop and restart the server to enable auditing.



Which version of SQL are you currently using? If you are using SQL2000 you can also switch on "c2 audit mode". This gives you the following (also taken from books online).

c2 audit mode Option
In Microsoft® SQL Server™ 2000, use the c2 audit mode option to review both successful and unsuccessful attempts to access statements and objects. With this information, you can document system activity and look for security policy violations.

C2 auditing tracks C2 audit events and records them to a file in the \mssql\data directory for default instances of SQL Server 2000, or the \mssql$instancename\data directory for named instances of SQL Server 2000. If the file reaches a size limit of 200 megabytes (MB), C2 auditing will start a new file, close the old file, and write all new audit records to the new file. This process will continue until SQL Server is shut down or auditing is turned off.



Hope this helps. SQL Books Online is a great resource for information like this.

 

[sixpot]

Cheers Jester777, that's exactly what I was after. I've also found and downloaded books online - excellent! Hopefully I can avoid those RTFM questions now...

 

[osjohnm]

Sixpot

One thing to keep in mind, audit has a very negative impact on server performancr, especially if you track everything.
Here is an extract from BOL warning about this:

"Auditing can have a significant performance impact. If all audit counters are turned on for all objects, the performance impact could be high. It is necessary to evaluate how many events need to be audited compared to the resulting performance impact. Audit trail analysis can be costly, so it is recommended that audit activity be run on a server separate from the production server."

John

 

[webuser]

If you need to audit for more specific items, there is a product called Lumigent LogExplorer, that can basically read the Transaction Log. You can do all sorts of things with this such as see which users updated what data etc.. It also allows you to rollback transactions... I think their web address is

http://www.lumigent.com.

This may be overkill for what you are looking for though...