| Author |
Topic |
|
mfemenel
Professor Frink
1421 Posts |
 Posted - 2005-10-28 : 09:42:53
|
| Good morning-Got a quick admin type question. Somehow our prod support people were added to our test/dev database and they shouldn't have been. I'd like to find out who added them and when. Any ideas? I tried sysusers but that seems to only show if the user has specific database permissions. These guys were added as sysadmins so they're not in a database specifically. thanks.Mike"oh, that monkey is going to pay" |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-10-28 : 09:51:04
|
| Did you see that in Profiler?MadhivananFailing to plan is Planning to fail |
 |
|
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
Posted - 2005-10-28 : 09:53:03
|
quote:
Originally posted by mfemenel
Good morning-Got a quick admin type question. Somehow our prod support people were added to our test/dev database and they shouldn't have been. I'd like to find out who added them and when. Any ideas? I tried sysusers but that seems to only show if the user has specific database permissions. These guys were added as sysadmins so they're not in a database specifically. thanks.Mike"oh, that monkey is going to pay"
syslogins is going to give you the "when". I will need to think about the "who". At the moment I'm thinking the "who" would need to be captured in either a trigger or profile, so you may be out of luck...Daniel, MCP, A+SQL Server DBAwww.dallasteam.com |
|
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2005-10-28 : 10:07:10
|
| Ok, well, the when is a good start at least. Since it's a dev/uat box we don't run profiler on a regular basis unless we're checking out performance so I guess we're out of luck.Mike"oh, that monkey is going to pay" |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2005-10-28 : 10:31:30
|
| How many sa's do you have registered to those boxes? Round up the usual suspects and grill them...I would also make sure that you have a limited number of people with sa.I'll give out dbo, but if they need more, they have to come to me or only a handful of people. Can you imagine the damage inflicted....[Apocalypse Now]The horror....[/Apocalypse Now]Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
|
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2005-10-28 : 11:00:48
|
| The problem is their manager requested that they have admin privelages to the box. Their manger however doesn't own the boxes, we do. I was trying to get the date it happened so I could track down the request and prove that the manager did this and shouldn't have. I found a different way though. Forunately in our ticket system you can look for tickets by first & last name and I just found where she made the request. Now the fun starts.Mike"oh, that monkey is going to pay" |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2005-10-28 : 11:49:19
|
| Well I'm not Tara...wtf?She is in IT but she's prod support and she requested admin access to all of our dev/uat boxes, then removed our rights.Mike"oh, that monkey is going to pay" |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2005-10-28 : 12:24:37
|
quote:
Originally posted by mfemenel
Well I'm not Tara...wtf?She is in IT but she's prod support and she requested admin access to all of our dev/uat boxes, then removed our rights.Mike"oh, that monkey is going to pay"
I apologized, because it's a bias thing...if she's not the owner of the boxes, how can she remove your rights? And hand it out to someone else? Expecially if she's in Product support?Product support?oye.....Did they downsize and have no one left to admin dev and QA?If I were you, I'd start betting on deadlines...good way to supplement incomeBrett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-10-29 : 12:08:18
|
| It sounds like production support is in the local administrator group somehow (possibly through an AD group memership)? You need to remove the BUILTIN\Administrator login from SQL Server and restrict who has sysadmin and securityadmin rights on the SQL Server. In addition, set up an ongoing Profiler trace to track the additions to permissions. Then, the next time they try something like this it will fail. If it does succeed, you will know exactly who did it and can proceed on dragging the idiots to HR. Have fun.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-10-30 : 02:35:42
|
| Sounds like you've found a way, but if you have transaction log backups on the database you might be able to get something from those - the "when" is the column createdate in sysusers, and that will lead you to which TLog backup file I guess.Kristen |
|
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2005-10-31 : 08:39:58
|
| Yeah, I can't remove them for now witout getting a hand slap from the boss. He wants to handle this all through "proper process".Mike"oh, that monkey is going to pay" |
|
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2005-10-31 : 13:34:34
|
| Thanks guys. Everything is locked down pretty well now. One question. On one server I didn't have a builtin\administrators but did have an NT AUthority\System. That's pretty much the same thing, right?Mike"oh, that monkey is going to pay" |
|
|
|
|
Find who/when a user was created