October 4, 2007 at 12:41 pm
Due to policy, i cannot take out 'SA' roles for other 3 users. i am getting lot of complaints that user privilege gets change from data_reader to DBO, so now they can update data, as earlier they were doing only SELECT.
How i can track & log, who is changing which user permission\privilege on what database along date, time & that user ID who changed it. so that i can catch them. i know this sound stupid but due to policy, i have to keep those trouble maker users.
Any help on this is highly appreciated.
Thanks in advance.
October 4, 2007 at 2:05 pm
You'll want to set up server side tracing. You can script an appropriate trace using Profiler and then apply it to the server. You'll have to review these traces in order to catch what's happening.
K. Brian Kelley
@kbriankelley
October 4, 2007 at 2:10 pm
doyou think that SQL 2005 DDL triggers are or will be helpfull on this matter.
October 4, 2007 at 2:24 pm
I'm not sure an event fires for modifying role. So you may not be able to do so. Are they granting other users the ability to do more than SELECT? If they aren't they already have the equivalent of dbo rights...
K. Brian Kelley
@kbriankelley
October 4, 2007 at 2:25 pm
Sorry, "modifying role" isn't clear. An event fires for ALTER ROLE. However, using sp_addrolemember to add a user account to a role may not fire an event.
K. Brian Kelley
@kbriankelley
October 4, 2007 at 2:40 pm
To Audit, you can use Sql servers C2 audit feature that will logon all failed and successful logins. To take this into affect you have to restart the server and use below commands
USE master
EXEC sp_configure 'show advanced option', '1'
RECONFIGURE
sp_configure 'c2 audit mode', 1
go
These files reside in \mssql\data directory. But be careful you can quickly run out of disk space as log files will be huge.
October 4, 2007 at 2:55 pm
Mayank Khatri (10/4/2007)
To Audit, you can use Sql servers C2 audit feature that will logon all failed and successful logins. To take this into affect you have to restart the server and use below commands
This is a pretty brutal server side trace. You can pare this down significantly by creating your own server side trace.
K. Brian Kelley
@kbriankelley
October 4, 2007 at 4:39 pm
To all replies:-
i cannot take away 'sysadmin roles' from other 3 users, due to stupid mgnt policy. Not sure how C2 Audit is going to help, as i want to catch that particular user who is modifying users role without change management & causing un-rquired update on PRODUCTION. once again, i am sorry that this sound stupid, but its not in my control. once i catch that user, then some changes can be brought.
Again any help is appreciated & thanking you for your all time & efforts.
Thanks
October 5, 2007 at 6:32 am
C2 auditing is a comprehensive server-side trace. It's going to record all activity. Meaning if someone execute a command to change the owner of an object, execute sp_addrolemember, etc., you're going to capture it in the trace. That provides a detective control (to use auditor speak).
K. Brian Kelley
@kbriankelley
October 5, 2007 at 7:53 am
Greetings,
The only way is to create a trace with a filter on those users. Make that trace auto-start when the SQL server service starts.
HOWEVER, be aware your users with SA authority can screw up so much that they can
a) stop the trace or take it down or delete it
b) bring down the server in a way the trace won't trace it
I strongly suggest you talk to management and explain them that SA privileges are only for DBA work and you can narrow their access rights to a level they can do their job. Do not forget those users could (depending of the surface area) use xp_cmdshell and do outside damage of the SQL server instance.
Moreover, on a side subject, professionally, you expose yourself to a pretty much bad situation having other people having SA authority on your SQL servers. If the servers go down or are having "stuff" disapearing, who will be blamed? Or who will have to proove who did what wrong? It is a lose lose situation for you.
Thank you
MBA
MCSE, MCDBA, MCSD, MCITP, IBM DB2 Expert, I-Net+, CIW
Proud member of the NRA
-Anti-gun laws prevent law abiding citizens to buy guns and defend themselves against bad guys who do not care about the law and get their gun illegally.
- Democracy is 2 wolves and one sheep talking about their next dinner. Freedom is 2 wolves and one armed sheep with a .357 magnum talking about their next dinner.
October 5, 2007 at 8:26 am
kestack makes a good point. Your management now has a repudiation issue since folks have sysadmin access (and are knowingly abusing that access) because you can't prove "who did it." And you won't without going to great lengths that could sap performance from your SQL Server.
K. Brian Kelley
@kbriankelley
October 5, 2007 at 12:30 pm
I got better understanding about C2 audit, but besides trace, do we or can i have customise Triggers in place which can take of the issue that i have. if not then i am depend on C2 or i have to look for any 3rd party software that can help me to resolve.
Once again thanks for your all helps.
October 8, 2007 at 9:42 pm
DDL triggers can flag on schema changes. While there is an event for changing the membership of a role, this isn't something accessible via DDL triggers. However, you can create an event response.
Yes, there are various 3rd party tools out there which do this sort of auditing. Network-based ones can even capture the IP address (which allows you to narrow down who did it).
K. Brian Kelley
@kbriankelley
October 8, 2007 at 10:00 pm
An Event Notification should fire when a role is modified, either sp_addrole or ALTER ROLE.
I'll try to test this tomorrow, but that might help you to trap who it is.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply