How to get the script of the logon trigger

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Security » How to get the script of the logon trigger

How to get the script of the logon trigger

Rate Topic

Display Mode

Topic Options

Author

Message

ragusragu

Posted Thursday, August 05, 2010 2:42 PM

Forum Newbie

Group: General Forum Members
Last Login: Friday, August 10, 2012 5:56 AM
Points: 1, Visits: 32

**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

Jack Corbett

Posted Thursday, August 05, 2010 7:26 PM

SSChampion

Group: General Forum Members
Last Login: Today @ 3:07 PM
Points: 10,613, Visits: 11,959

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 »

Permissions