Monitoring which user accesses a database

Question

Monitoring which user accesses a database

Barkingdog

SSCoach

Points: 18670
More actions
January 27, 2004 at 8:54 am

#76116

Recently, one of our project managers started complaining that someone is modifying data in certain Sql (2k) production tables under his group's control. I have reviewed users authorized to access the database and it is only his group... but I can see it coming.
How can I monitor, in a reasonable way, Login IDs, the tables they access, and the date\time of such access?
Any ideas, much appreciated.

TIA,
Bill

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Jeff W SSCertifiable Points: 5207 More actions · Answer 1

Bill...not sure if I am going to offer what you are looking for. This is a thread I will probably follow too. Here are a couple of ideas that come to mind.

**Create triggers on the tables that log the user, date, and transaction to a table. I don't like this idea but think it would work.

**On some of my databases I create extra columns at the end of the row. I do this when their are legal implications regarding the data. For example, the Federal Do Not Call list. These columns include:

Created (Date created)
CreatedBy (Account that created the record)
Modified (Date data in the row was last modified)
ModifiedBy (Account that last modified the data)

These rows are populated with triggers and DEFAULTS.

**Another idea is to use Profiler and watch..Probably too much data to capture.

**You may possibly also be able to create an app that catches the spid of the users when they are logged in and logs the results of the DBCC INPUTBUFFER(spid) command.

"Keep Your Stick On the Ice" ..Red Green

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 2

Profiler is the least intrusive. You can setup a trace for a specific database and a specific objectid, like a table and then catch the person in the act.

Jeff W SSCertifiable Points: 5207 More actions · Answer 3

Steve..

I can do this in Oracle and have yet to find a way in SQL Server. Is there a way to activate a trigger on login, or create an alert for a specific login?

Jeff

"Keep Your Stick On the Ice" ..Red Green

Joe Clifford SSCrazy Eights Points: 8739 More actions · Answer 4

If you've got a few $ why not look into a true auditing program like Entegra?

Barkingdog SSCoach Points: 18670 More actions · Answer 5

Thanks to everyone for their replies. Also, I had not heard of "Integra". I'll check it out.

Bill

fhanlon SSChampion Points: 13131 More actions · Answer 6

There are quite a lot a auditing applications available- OmniAudit, ApexSQL Audit, SQL Smart Audit, SQLAUDIT, DBAudit. They all differ in cost, where the audit data is stored (same database, different database, XML file) what they can audit- just SQL Server or any ODBC available database. Entegra is about the only product that can audit Selects as well as the Inserts/Deletes that everyone else audits. As a result Entegra is very expensive. If you don't need the ability to log SELECTS and you can't afford the price tag try one of the other products. Your other alternative is to write you own tirggers for which there is a starter script here: http://www.sqlservercentral.com/scripts/contributions/521.asp

Francis

vambati SSCrazy Points: 2769 More actions · Answer 7

Why don't you turn Audit on that box and then have triggers or other tools suggested by others. So that u have data to comapre who logged in and what he did.

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 8

I don't know of a way to alert on a particular user. You can alert on login, but I don't think that will help you.

If this is an ongoing issues, Entegra might be nice (from Lumigent), but if it's a onsy twosy thing and you want to handle it administratively, I think Profiler might be the best bet.