SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


A trigger prevent user from update/delete a user login


A trigger prevent user from update/delete a user login

Author
Message
Gianluca Sartori
Gianluca Sartori
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22851 Visits: 13361
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
robert rogers-466427
robert rogers-466427
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 674
Is there a policy!
ScottPletcher
ScottPletcher
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18746 Visits: 7402
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search