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: Today @ 1:33 AM
Points: 5,029, Visits: 10,561
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

Get your two-cent-answer quickly
Post #1510349
Posted Thursday, October 31, 2013 11:35 AM


Group: General Forum Members
Last Login: Yesterday @ 9:45 AM
Points: 18, Visits: 560
Is there a policy!
Post #1510360
Posted Friday, November 1, 2013 2:30 PM


Group: General Forum Members
Last Login: Yesterday @ 4:13 PM
Points: 2,087, Visits: 3,137
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)

"While in these days of quiet desperation /
As I wander through the world in which I live /
I search everywhere for some new inspiration /
But it's more than cold reality can give /
If I need a cause for celebration /
Or a comfort I can use to ease my mind /
I rely on my imagination /
And I dream of an imaginary time" : the inimitable Mr. Billy Joel
Post #1510812
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse