Prevent Logon Triggers

Question

Prevent Logon Triggers

onetsql

Valued Member

Points: 57
More actions
January 21, 2019 at 9:44 am

#370256

Hi,
Does anybody know of a way to prevent logon triggers from been created or from firing? We had a user create a logon trigger (thankfully on a test server) which as expected prevented all users from connecting to SQL. The issue was fixed by using a DAC connection and disabling the trigger but I was wondering if there was anything more proactive we could do to prevent this from happening.
I was looking at advanced options and Policies but can't find anything out of the box that we can use.
Thanks

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Sue_H SSC Guru Points: 90860 More actions · Answer 1

onettleton - Monday, January 21, 2019 9:44 AM
Hi,
Does anybody know of a way to prevent logon triggers from been created or from firing? We had a user create a logon trigger (thankfully on a test server) which as expected prevented all users from connecting to SQL. The issue was fixed by using a DAC connection and disabling the trigger but I was wondering if there was anything more proactive we could do to prevent this from happening.
I was looking at advanced options and Policies but can't find anything out of the box that we can use.
Thanks

Things like that are generally managed with permissions. Seems like that user has more permissions than needed - especially when you look at the results of what they did.
A user needs control server permissions for logon triggers. Or a sysadmin.
If the user(s) is a sysadmin, you can't deny permissions. And you likely have a problem to address even when in non-production environments.

Sue

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 2

I agree with Sue. You need to fix the permissions on the users of those machines so that can't do such a thing.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)