Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to get the script of the logon trigger Expand / Collapse
Author
Message
Posted Thursday, August 5, 2010 2:42 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 1, 2014 1:43 AM
Points: 2, Visits: 33
**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.
Post #964713
Posted Thursday, August 5, 2010 7:26 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:07 AM
Points: 11,157, Visits: 12,899
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #964796
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse