logon triggers featuring EXECUTE AS is a good practice?

• Lowell

  SSC Guru

  Points: 323518

  More actions

  January 3, 2011 at 2:25 pm

  #229241

  I've been fiddling with logon triggers again, and tripped over a few permissions issues where end users don't have rights to email, or write to an audit table in a certain database, etc.

  As a result, I've gotten into using a syslogin I created specifically for that purpose...disabled the login, but am using it for EXECUTE AS functionality to overcome these types of issues;

  Are you doing the same thing with your logon triggers? Am I using a hammer to swat a fly by using over-reaching permissions?

  /****** Object: Login [superman] Script Date: 01/03/2011 16:17:48 ******/

  CREATE LOGIN [superman] WITH PASSWORD=N'NotARealpassword', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=ON, CHECK_POLICY=ON

  GO

  EXEC sys.sp_addsrvrolemember @loginame = N'superman', @rolename = N'sysadmin'

  GO

  ALTER LOGIN [superman] DISABLE

  GO

  CREATE TRIGGER TR_Logon_Trigger

  ON ALL SERVER FOR LOGON

  AS

  BEGIN

  EXECUTE AS LOGIN='superman'

  --do stuff in multiple databases that an end user has no rights to

  END --Trigger

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• GSquared

  SSC Guru

  Points: 260824

  More actions

  January 3, 2011 at 2:42 pm

  #1268683

  I generally have login triggers use a sysadmin account. If you don't, and the trigger ends up blocking you from logging in, it's gets really messy really fast.

  - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
  Property of The Thread

  "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

• Johan Bijnens

  SSC Guru

  Points: 135369

  More actions

  January 3, 2011 at 3:12 pm

  #1268691

  Please read my little article called "Scope: The drastic caveat with Logon Triggers. " at http://www.sqlservercentral.com/articles/Administration/64974/

  Main message: If you don't need to interrupt the connection at connect time, don't do it using a logon trigger !

  Johan
  
  Learn to play, play to learn !

  Dont drive faster than your guardian angel can fly ...
  but keeping both feet on the ground wont get you anywhere :w00t:

  - How to post Performance Problems
  - How to post data/code to get the best help[/url]

  - How to prevent a sore throat after hours of presenting ppt

  press F1 for solution, press shift+F1 for urgent solution 😀

  Need a bit of Powershell? How about this

  Who am I ? Sometimes this is me but most of the time this is me