Sample script below:
1: -- ==========================================================================
2: -- Object : DDLTrigger.sql
3: -- Object Type : Script
4: -- Description : Various examples
5: -- Notes :
6: -- ==========================================================================
7:
8: -- DDL Triggers
9: -- ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/d79e5725-adea-4934-9474-9cd975adb6d8.htm
10:
11: -- DDL Trigger Events
12: -- http://msdn.microsoft.com/en-us/library/ms189871.aspx
13:
14: -- EVENTDATA
15: -- ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/03a80e63-6f37-4b49-bf13-dc35cfe46c44.htm
16: -- EVENTDATA function returns XML data which contains the information about server
17: -- or database events.
18:
19: -- sample EVENTDATA()
20: /*
21: <EVENT_INSTANCE>
22: <EventType>CREATE_TABLE</EventType>
23: <PostTime>2008-10-05T01:23:12.030</PostTime>
24: <SPID>55</SPID>
25: <ServerName>JUBILEESQL01</ServerName>
26: <LoginName>JUBILEEAdministrator</LoginName>
27: <UserName>dbo</UserName>
28: <DatabaseName>AdventureWorks</DatabaseName>
29: <SchemaName>dbo</SchemaName>
30: <ObjectName>t</ObjectName>
31: <ObjectType>TABLE</ObjectType>
32: <TSQLCommand>
33: <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON"
34: QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
35: <CommandText>CREATE TABLE t
36: (
37: id int
38: )
39: </CommandText>
40: </TSQLCommand>
41: </EVENT_INSTANCE>
42:
43: */
44:
45: -- ---------------------------------------------------------------------
46: -- database scope
47: -- SSMS: Database > Programmability > Database Triggers
48: -- ---------------------------------------------------------------------
49: USE AdventureWorks
50: GO
51:
52: IF EXISTS(
53: SELECT *
54: FROM sys.triggers
55: WHERE name = N'tr_db_ddl_NoCreateTable'
56: AND parent_class_desc = N'DATABASE'
57: )
58: BEGIN
59: DROP TRIGGER tr_db_ddl_NoCreateTable ON DATABASE
60: END
61: GO
62:
63: CREATE TRIGGER tr_db_ddl_NoCreateTable
64: ON DATABASE -- current database
65: FOR CREATE_TABLE, DROP_TABLE
66: AS
67: BEGIN
68: SELECT
69: 'tr_db_ddl_NoCreateTable' 'Trigger',
70: 'You are not permitted to perform selected action' 'Msg2',
71: EVENTDATA() 'EVENTDATA()'
72: ROLLBACK
73: END
74: GO
75:
76: -- test
77: CREATE TABLE t
78: (
79: id int
80: )
81: GO
82:
83:
84: -- ---------------------------------------------------------------------
85: -- server scope
86: -- SSMS: Server Objects > Database Triggers
87: -- ---------------------------------------------------------------------
88: USE AdventureWorks
89: GO
90:
91: IF EXISTS(
92: SELECT *
93: FROM sys.server_triggers
94: WHERE name = N'tr_srv_ddl_DisplayServerEvents'
95: )
96: BEGIN
97: DROP TRIGGER tr_srv_ddl_DisplayServerEvents ON ALL SERVER
98: END
99: GO
100:
101:
102: CREATE TRIGGER tr_srv_ddl_DisplayServerEvents
103: ON ALL SERVER
104: FOR CREATE_DATABASE, CREATE_LOGIN
105: AS
106: BEGIN
107: SELECT
108: 'tr_srv_ddl_DisplayServerEvents' 'Trigger',
109: 'You are not permitted to perform selected action' 'Msg',
110: EVENTDATA() 'EVENTDATA()'
111: ROLLBACK
112: END
113: GO
114:
115: -- test
116: CREATE DATABASE db
117: GO
It is also interesting to note that creating a server-scoped trigger for DDL_SERVER_SECURITY_EVENTS
Msg 1082, Level 15, State 1, Procedure tr_srv_ddl_DisplayServerEvents, Line 11
“DROP_SERVER_ROLE_MEMBER” does not support synchronous trigger registration.
The trigger that fails follows:
1: CREATE TRIGGER tr_srv_ddl_DisplayServerEvents
2: ON ALL SERVER
3: FOR DDL_SERVER_SECURITY_EVENTS
4: AS
5: BEGIN
6: SELECT
7: 'tr_srv_ddl_DisplayServerEvents' 'Trigger',
8: 'You are not permitted to perform selected action' 'Msg',
9: EVENTDATA() 'EVENTDATA()'
10: ROLLBACK
11: END
12: GO
13:
Not sure why. Seems the DDL_SERVER_SECURITY_EVENTS should include only server-scoped events.
Will continue to dig in; when I find additional details I will post a follow up entry to this one.
1 Comment
