<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss Content Posted by David Poole
»
Tracking Illicit Users
32 posts, Page 1 of 4
1
2
3
4
»
»»
Tracking Illicit Users
Rate Topic
Display Mode
Topic Options
Author
Message
David.Poole
David.Poole
Posted Monday, January 14, 2008 9:44 PM
SSCrazy
Group: General Forum Members
Last Login: Thursday, May 16, 2013 3:16 PM
Points: 2,749,
Visits: 1,405
Comments posted to this topic are about the item
Tracking Illicit Users
LinkedIn Profile
Post #442812
Jeff Moden
Jeff Moden
Posted Monday, January 14, 2008 9:47 PM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 12:30 PM
Points: 32,893,
Visits: 26,770
Heh... I take the low road... disable all logins (except mine), answer the phone to see who's squawkin' ;) (just kidding... in
most
cases :P )
Great article, David!
--Jeff Moden
"
RBAR
is pronounced "ree-bar" and is a "Modenism" for "
R
ow-
B
y-
A
gonizing-
R
ow".
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."
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #442813
Krissy
Krissy
Posted Monday, January 14, 2008 10:19 PM
SSC Veteran
Group: General Forum Members
Last Login: Wednesday, December 19, 2012 6:40 PM
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 :P) 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
:D
Post #442818
Christian Buettner-167247
Christian Buettner-167247
Posted Tuesday, January 15, 2008 12:53 AM
SSCrazy
Group: General Forum Members
Last Login: Thursday, May 16, 2013 3:36 AM
Points: 2,522,
Visits: 3,616
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
Post #442837
GilaMonster
GilaMonster
Posted Tuesday, January 15, 2008 1:12 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 4:15 PM
Points: 37,651,
Visits: 29,903
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)
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. :D
Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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
Post #442841
Jeff Moden
Jeff Moden
Posted Tuesday, January 15, 2008 5:15 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 12:30 PM
Points: 32,893,
Visits: 26,770
Clever... effective... and vengeful... all in the same action... I LIKE IT! :P
--Jeff Moden
"
RBAR
is pronounced "ree-bar" and is a "Modenism" for "
R
ow-
B
y-
A
gonizing-
R
ow".
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."
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #442911
Andy Warren
Andy Warren
Posted Tuesday, January 15, 2008 5:31 AM
SSCertifiable
Group: Moderators
Last Login: Thursday, May 09, 2013 12:38 PM
Points: 6,462,
Visits: 1,384
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
SQLShare - Learn One New Thing Each Day
SQLAndy - My Professional Blog
Connect with me on LinkedIn
Follow me on Twitter
Post #442916
Joseph Hicks-446980
Joseph Hicks-446980
Posted Tuesday, January 15, 2008 7:08 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Thursday, June 04, 2009 9:38 AM
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
Post #442977
Sanjeev Jha-475652
Sanjeev Jha-475652
Posted Tuesday, January 15, 2008 7:16 AM
SSC Rookie
Group: General Forum Members
Last Login: Thursday, March 28, 2013 8:39 AM
Points: 30,
Visits: 136
lOVE the article. I am going to practice and learn from your expertise. Thanks for making us wiser at what we do.
-Sanjeev
Post #442981
Sorin Petcu
Sorin Petcu
Posted Tuesday, January 15, 2008 8:12 AM
SSChasing Mays
Group: General Forum Members
Last Login: Monday, April 29, 2013 7:32 AM
Points: 620,
Visits: 267
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?
In Theory, theory and practice are the same...In practice, they are not.
Post #443028
« Prev Topic
|
Next Topic »
32 posts, Page 1 of 4
1
2
3
4
»
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
