Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Tracking Illicit Users


Tracking Illicit Users

Author
Message
David.Poole
David.Poole
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3680 Visits: 3115
Comments posted to this topic are about the item Tracking Illicit Users

LinkedIn Profile

Newbie on www.simple-talk.com
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45086 Visits: 39912
Heh... I take the low road... disable all logins (except mine), answer the phone to see who's squawkin' Wink (just kidding... in most cases Tongue )

Great article, David!

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Krissy
Krissy
SSC Veteran
SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)

Group: General Forum Members
Points: 279 Visits: 551
I pretty much use an identical script to stop developers accessing the live server.

Unfortunately I can't disable their logins (as much as I would like to Tongue) as they are allowed (by the business for 'support' purposes) to use the standby/logshipping server to access data and since permissions are replicated across, they have to remain in place.

The main reason we don't want them accessing the live server is so they don't change data (in the case where they know the account password from a legacy system) and also so they don't put load on the server if they run a massive query.

The best part of my script - KILL @SPID BigGrin
Christian Buettner-167247
Christian Buettner-167247
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2949 Visits: 3889
Hello,

could you please tell my why you would not use profiler to audit logins?
Is this putting too much burden on the server?

Thanks!

Best Regards,

Chris Büttner
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47245 Visits: 44377
I had much the same problem some months back. Group of developers felt they were above the rules and were fiddling on Prod using a SQL account for a messaging application that they were maintaining.

Couldn't disable the account, cause the messaging is a critical process (inter-bank money transfers). Could change the password, but would have had to tell developers so that they could configure the messaging app with new password (and the app keeps the password clear-text in it's properties) Crazy

The security officer threatened them with diciplinary hearing and possible dismissal, but management did nothing more than slap them on the wrists. They were 'valuable employees'

When SQL 2005 SP2 came out, I dumped a login trigger on the prod box that rolls back any connections that cme from the app's login that use a querying tool.

The screams were soooo pleasant to hear. BigGrin


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45086 Visits: 39912
Clever... effective... and vengeful... all in the same action... I LIKE IT! Tongue

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Andy Warren
Andy Warren
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: Moderators
Points: 7227 Visits: 2679
Good stuff as always David!

My two cents; firewall rules to block developers from direct access to production is a very cool thing, and probably easier to sell in the SOX era than killing the legacy apps. Doesn't eliminate the problem, but makes it hard for them to abuse. The other point is that hostname can be spoofed on purpose or accidentally. I don't know if it still does it, but when you linked a table in Access it would store the host name as part of the connection, so if you copied the MDB to someone else it looked like they were running from the original machine.

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Joseph Hicks-446980
Joseph Hicks-446980
SSC-Enthusiastic
SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)

Group: General Forum Members
Points: 128 Visits: 21
Andy Warren (1/15/2008)
... The other point is that hostname can be spoofed on purpose or accidentally ...


We had a group of developers (from before my employment) use this to identify the class of a process. Unfortunately for me, this was then used in other processes to automate other processes, so now I'm stuck in a sea of "I can't tell which computers the connections are coming from", so I've turned to using MAC addresses (sysprocesses.net_address). I'd think this would be a much more reliable method of determining which computers are connecting, but I know almost nothing of MAC addresess and haven't figured out how to tie them to an IP address without explicitly checking the MACs of each of our computers.

/// Edit to add source of MAC addresses
Sanjeev Jha-475652
Sanjeev Jha-475652
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 197
lOVE the article. I am going to practice and learn from your expertise. Thanks for making us wiser at what we do.

-Sanjeev
Sorin Petcu
Sorin Petcu
Say Hey Kid
Say Hey Kid (680 reputation)Say Hey Kid (680 reputation)Say Hey Kid (680 reputation)Say Hey Kid (680 reputation)Say Hey Kid (680 reputation)Say Hey Kid (680 reputation)Say Hey Kid (680 reputation)Say Hey Kid (680 reputation)

Group: General Forum Members
Points: 680 Visits: 346
The line:
INSERT INTO @InputBuffer(EventType,Parameters,EventInfo) EXEC (@SQL)
gave me an error:
Server: Msg 197, Level 15, State 1, Line 65
EXECUTE cannot be used as a source when inserting into a table variable.

What happens?
Crying

In Theory, theory and practice are the same...In practice, they are not.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search