Re: Logon triggers

"However, it's not that if you mess up the login trigger that no one can
connect at all. You can still connect on the DAC (Dedicated Administrator
Connection) to drop the trigger. Either with SQLCMD -A or specify ADMIN:
before the server in SSMS. But keep in mind that the DAC is one single
connection, and by default you can only connect locally on the DAC."

Was the information I was looking for. Now I know the risks of using logon
triggers and how to workaround those risks. I'll take a more detailed look on
Event Notifications since in the future I want to log DDL & SQL Config
changes as well.



"Erland Sommarskog" wrote:

Hugo (Hugo@xxxxxxxxxxxxxxxxxxxxxxxxx) writes:
I was 'afraid' that was gone be the answer. The howto use Event
Notifications is four times as long as the howto on Logon Triggers.

I understand Event Notifications is much more powerfull and can do a lot
more. Some features I will be looking at for use in the future.

It usually not a good idea to choose a feature from the length of the
tutorial.

It all depends on what you want to achieve. If you want to prevent logins
for some reason, for instance during certain hours, logon triggers is the
way to go. Same if you want fire-proof auditing, and you cannot permit a
login happening without being logged.

On the other hand, if the login action is only nice-to-have, and should
never prevent login from happening, event notifications is the way to go.

However, it's not that if you mess up the login trigger that no one can
connect at all. You can still connect on the DAC (Dedicated Administrator
Connection) to drop the trigger. Either with SQLCMD -A or specify ADMIN:
before the server in SSMS. But keep in mind that the DAC is one single
connection, and by default you can only connect locally on the DAC.

--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx


.

Relevant Pages

  • Re: Creating a Trigger on Access
    ... I don't think a 'SELECT' trigger would be a good idea since data could be ... access for reasons other than login. ... a stored procedure would be the ... DECLARE @ReturnCode int ...
    (microsoft.public.sqlserver.security)
  • Re: are triggers atomic?
    ... If I write a trigger for a file, ... fact that it will conclude before another login on say port ... Maybe I should have been using a readu lock in my ...
    (comp.databases.pick)
  • Trigger**
    ... I've a following trigger which is fired in update status and make all ... destination table) ... ,but when I change the emp_status (by another login which has not access to ... table,an error occurred that you have no permission to update the second ...
    (microsoft.public.sqlserver.programming)
  • Re: login auditing
    ... trigger on that table. ... 2003 you can use account policies and limit users to login on certain hosts ... "Nishu" wrote in message ...
    (microsoft.public.sqlserver.security)