Mega Code Archive
Viewing DDL Trigger Metadata by joining sys server_sql_modules and sys server_triggers
<![CDATA[
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>
]]>