Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

A trigger prevent user from update/delete a user login Expand / Collapse
Author
Message
Posted Thursday, October 31, 2013 11:14 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 7:42 AM
Points: 4,417, Visits: 10,718
There's no DDL event for that as far as I know. Sorry.
Moreover, I still think it's not a good idea.


--
Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Post #1510349
Posted Thursday, October 31, 2013 11:35 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 7:54 AM
Points: 18, Visits: 566
Is there a policy!
Post #1510360
Posted Friday, November 1, 2013 2:30 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 9:20 AM
Points: 2,197, Visits: 3,309
Instead of DDL_LOGIN_EVENTS, you can use DDL_SERVER_SECURITY_EVENTS, which covers a much wider range of statements. See BOL, "DDL Event Groups" for more details.

You'll need to use EVENTDATA() from within the trigger to get info on the triggering command. You can do a test with "SELECT EVENTDATA()" in the trigger to see what the XML data looks like.

Within the XML, get the affected login from the "<ObjectName>" tag/value. Sorry, I'm not an XML jock, and don't have time now to provide the specific code on how to do that, but I'm sure someone else here can help you with that part.

Then, if it's not the specific login name you want to prevent mods to, just issue a RETURN statement to exit the trigger and allow normal processing to continue, since naturally you want a DDL trigger to run as efficiently as possible.

If it is the specific name, then you can do whatever messages, rollback, etc., you want to do. Btw, you should encrypt the trigger to make it at least a little harder for other sysadmins to modify. They can of course delete the trigger, but you could capture that with extended events ... unless they modify/remove those also. Ultimately there's no way to block an sa, but you can make it enough of a pain that most sa's won't bother/know how to get around the trigger(s).


SQL DBA,SQL Server MVP('07, '08, '09)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1510812
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse