SQL Server/T-SQL Tutorial/System Tables Views/sys.server sql modules

Viewing DDL Trigger Metadata by joining sys.server_sql_modules and sys.server_triggers

3>
4> SELECT t.name, m.definition
5> FROM sys.server_sql_modules m
6> INNER JOIN sys.server_triggers t ON
7> m.object_id = t.object_id
8> GO
name
         definition

------------------------------------------------------------------------------------------------------------------------
-------- ---------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
-------------------------
trg_RestrictNewLogins
         -- Disallow new Logins on the SQL instance
CREATE TRIGGER trg_RestrictNewLogins
ON ALL SERVER
FOR CREATE_LOGIN
AS
PRINT "No login creations without DBA involvement."
ROLLBACK
srv_trg_RestrictNewLogins

CREATE TRIGGER srv_trg_RestrictNewLogins
ON ALL SERVER
FOR CREATE_LOGIN
AS
SET NOCOUNT ON
PRINT "Your login creation is being monitored."
INSERT YourDatabaseName.dbo.MyAudit
(EventData, DBUser)
VALUES (EVENTDATA(), USER)
1>