Click here to monitor SSC
SQLServerCentral is supported by Redgate
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
Posted Thursday, October 31, 2013 11:14 AM



Group: General Forum Members
Last Login: Tuesday, November 22, 2016 6:32 AM
Points: 5,519, Visits: 13,287
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
Post #1510349
Posted Thursday, October 31, 2013 11:35 AM


Group: General Forum Members
Last Login: Tuesday, November 29, 2016 2:57 PM
Points: 19, Visits: 638
Is there a policy!
Post #1510360
Posted Friday, November 1, 2013 2:30 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 3:56 PM
Points: 3,852, Visits: 6,566
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)

Prosecutor James Blackburn, in closing argument in the "Fatal Vision" murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."
Post #1510812
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse