How to get the script of the logon trigger

• ragusragu

  Old Hand

  Points: 330

  More actions

  August 5, 2010 at 2:42 pm

  #387511

  **I'm using SQL Server 2005 with SP3

  **I used the below script from the SQL books online to test the logon trigger

  USE master;

  GO

  CREATE LOGIN login_test WITH PASSWORD = '3KHJ6dhx(0xVYsdf' MUST_CHANGE,

  CHECK_EXPIRATION = ON;

  GO

  GRANT VIEW SERVER STATE TO login_test;

  GO

  CREATE TRIGGER connection_limit_trigger

  ON ALL SERVER WITH EXECUTE AS 'login_test'

  FOR LOGON

  AS

  BEGIN

  IF ORIGINAL_LOGIN()= 'login_test' AND

  (SELECT COUNT(*) FROM sys.dm_exec_sessions

  WHERE is_user_process = 1 AND

  original_login_name = 'login_test') > 3

  ROLLBACK;

  END;

  **This works fine

  **I can alter, disable and delete this logon trigger

  How to get the script of the logon trigger if it was created by some other DBA. Is it possible to script this logon trigger from any of the system table/view.

  Thanks in advance.

• Jack Corbett

  SSC Guru

  Points: 184393

  More actions

  August 5, 2010 at 7:26 pm

  #1203209

  Here you go:

  SELECT

  SSM.definition

  FROM

  sys.server_triggers AS ST JOIN

  sys.server_sql_modules AS SSM

  ON ST.object_id = SSM.object_id

  Or you can just locate it in SSMS and Generate the create script

  Jack Corbett
  Consultant - Straight Path Solutions
  Check out these links on how to get faster and more accurate answers:
  Forum Etiquette: How to post data/code on a forum to get the best help
  Need an Answer? Actually, No ... You Need a Question