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

   <source lang="sql">

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></source>