Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jholovacs
Posting Yak Master
163 Posts |
 Posted - 2008-01-11 : 11:53:41
|
| I have ddl triggers in place to watch what people do to our various database environments. I can see when someone does something to a login, but I can't tell what was done. I have a sneaky someone creating accounts with sysadmin privs and I want to catch the source. I also want to know when someone changes a password on a sql account. Does anyone know of a way to do this?___________________________Geek At Large |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-01-12 : 00:30:05
|
| Only sysadmin can careate login with sysadmin rights, pretty small group. |
 |
|
|
jholovacs
Posting Yak Master
163 Posts |
Posted - 2008-01-14 : 08:20:30
|
| Yes, thank you, but not helpful. I am working to reduce the number of sysadmins (the server predates DBAs and there are a couple dozen developers who are in the machine daily... as sysadmin) but at the current time, all I can do is present a policy about what people can and cannot do. I need an audit infrastructure to catch offenders.I've heard some of this can work as a service broker. Has anyone tried this, and to what degree of success?-Jeremy___________________________Geek At Large |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-01-14 : 23:40:55
|
| Then you can trace those in profiler. |
|
|
|
jholovacs
Posting Yak Master
163 Posts |
Posted - 2008-01-15 : 09:16:30
|
| profiler != infrastructure. I have no intention of "watching" the server every day, I need the server to do its own watching and record actions that I need to take action on. I would appreciate suggestions in line with what I am asking for. I figured out a way to do it using service brokers, queues, and notifications that I shamelessly modified from BOL. Write me and I'll send you the document I made that covered the changes I put in.___________________________Geek At Large |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2008-01-15 : 10:18:29
|
Sounds like a potentially disasterous situation. Rather than developing and laying traps and trying to enforce policy you should take back control! Change passwords and don't give anyone but your trusted core of admin people access. The pain caused by re-working some security configuration is worth the protection you would gain. If you work at a bank tell me which it is so I can move my "vast fortune" to a secure environment  Be One with the OptimizerTG |
|
|
|
jholovacs
Posting Yak Master
163 Posts |
Posted - 2008-01-15 : 10:29:48
|
| Heh... no bank, just a startup... and I am taking control, but everyone is used to unfettered access (even the managers) so introducing a culture of change control and access restriction is a rocky road. I'm finally getting to a point where they have agreed to let me make all production changes, which is good; but making sure I'm the only one and reminding the few "rogues" out there that willy-nilly "adjustments" to production are unacceptable is a top priority... that's why I needed an auditing infrastructure, so I can let them know I'm watching them. I've already busted a few on schema changes, so they got sneaky and started using their sysadmin privs to change the sa pwd so i couldn't see who was making changes. I plan to put a stop to that pretty quick... :)___________________________Geek At Large |
|
|
|
|
|
|
|
|
DDL triggers to audit permission/ role chg