August 5, 2010 at 2:42 pm
**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.
August 5, 2010 at 7:26 pm
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply