Sysprocesses monitoring

Question

Post reply

Sysprocesses monitoring

Dan Larson

SSC Enthusiast

Points: 140
More actions
July 23, 2007 at 1:00 pm

#125122

SA access has been granted to a few too many people in our company and I've been asked to monitor/audit the number of times someone connects to our db server as sa. Coming from an oracle environment, I figured a simple db logon event trigger would work. Well, it would if I was monitoring oracle - but since I'm not, my next choice was the sysprocesses table. But after a little googling I realized that's not possible either.
Aside from polling the sysprocesses table via C# every 10 seconds or so and dumping what's different from the last poll into an audit table somewhere, is there any other way to watch who's connecting as sa over an extended period of time (couple weeks atleast)?
Polling the db server just seems a little inefficient to me.
Dan

Viewing 11 posts - 1 through 10 (of 10 total)

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

noeld SSC Guru Points: 96590 More actions · Answer 1

There are Logon triggers in SQL Server 2005!

http://msdn2.microsoft.com/en-us/library/bb326598.aspx

* Noel

Dan Larson SSC Enthusiast Points: 140 More actions · Answer 2

Sorry I should've noted that we're still stuck on Sql Server 2000.

noeld SSC Guru Points: 96590 More actions · Answer 3

Well I answered like that because this forum is for "2005".

For 2000 the best you can do is use an Alert!

I guess the the Error Numbers were 18453 and 18454 but you can check that out on your own

* Noel

Dan Larson SSC Enthusiast Points: 140 More actions · Answer 4

Alerts eh? I'll give that a look-see. Otherwise I'll just poll the heck out of the db and say it must be time for an upgrade!

Thanks!

noeld SSC Guru Points: 96590 More actions · Answer 5

don't get me wrong Alrets work fairly well. The reason I said "fairly" is that ususallly you would tie a job that records the alert content somewhere and if the "frequency" of "sa" logins is not too high it works perfectly, but if the frequency is high then you may lose a couple of attemps. Notice that normally you would expect that not a lot of people are using "sa"

* Noel

noeld SSC Guru Points: 96590 More actions · Answer 6

noeld

SSC Guru

Points: 96590

July 23, 2007 at 1:41 pm

#721653

Oh, and you are welcome

* Noel

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 7

i have a same requirement and i have written a service to track all these send me your id i shall send the code to you.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 8

If you do not need a service i have a program of server side tracing also i can send that too.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

Mark Shvarts 1 SSCertifiable Points: 5702 More actions · Answer 9

You can also use SQL Server profiler. You can choose security audit/ audit login event and filter it to Loginname=sa.

Dan Larson SSC Enthusiast Points: 140 More actions · Answer 10

Noel - is it possible to retrieve the error message from the alert and pass it to a job? I see that error number 18453 has the variable %ls (obviously the user account name) but I'm not sure how I'd pass that to a job if I needed to. Is this doable? Not sure how I'd use this yet, but the melon's already craftin' some ideers.