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
Posted Thursday, October 31, 2013 11:14 AM



Group: General Forum Members
Last Login: Yesterday @ 1:41 AM
Points: 5,191, Visits: 12,261
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: Wednesday, July 29, 2015 2:56 PM
Points: 18, Visits: 598
Is there a policy!
Post #1510360
Posted Friday, November 1, 2013 2:30 PM


Group: General Forum Members
Last Login: Yesterday @ 4:40 PM
Points: 2,957, Visits: 4,513
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