In my Are you ready for SQL Server 2012 or are you still partying like it is 1999? post, I wrote about how you should start using SQL Server 2005 and SQL Server 2008 functionality now in order to prepare for SQL Server 2012. I still see tons of code that is written in the pre 2005 style and people still keep using those functions, procs and statements even though SQL Server 2005 and 2008 have much better functionality.
Today we are going to take a look at DDL triggers. DDL triggers have Server or Database Scope, DDL triggers can fire in response to a T-SQL event processed in the current database, or on the current server. For example a CREATE_TABLE event can fire on a database level as well as on a server level, a DROP_DATABASE can only fire on the server level. DDL triggers can be a very powerful tool for auditing purposes as well.
Let’s take a quick look at a DDL trigger for a CREATE_TABLE event, this trigger will fire every time someone issues a CREATE TABLE T-SQL DDL statement
This trigger is created to capture all CREATE_TABLE events in the tempdb database
USE tempdb
GO
CREATE TRIGGER DBLevelsafety
ON DATABASE
FOR CREATE_TABLE
AS
PRINT 'You must disable Trigger "DBLevelsafety" to drop or alter tables!'
ROLLBACK
;
If you try to create a table now in the tempdb database
USE tempdb
GO
CREATE TABLE bla(id int)
You will get the following message
_You must disable Trigger “DBLevelsafety” to drop or alter tables!
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted._
Let’s drop the trigger
DROP TRIGGER DBLevelsafety
_Msg 3701, Level 11, State 5, Line 1
Cannot drop the trigger ‘DBLevelsafety’, because it does not exist or you do not have permission._
In order to drop a DDL trigger, the syntax is a little different from a DML trigger, you need to add ON DATABASE or ON ALL SERVER after the name, Try it again
DROP TRIGGER DBLevelsafety ON DATABASE
Now create a DDL triggers that will fire in any database.
CREATE TRIGGER ServerLevelsafety
ON ALL SERVER
FOR CREATE_TABLE
AS
PRINT 'You must disable Trigger "ServerLevelsafety" to drop or alter tables!'
ROLLBACK
;
Go into the model database and try to create a table
USE model
GO
CREATE TABLE temp(id int)
_You must disable Trigger “ServerLevelsafety” to drop or alter tables!
Msg 3609, Level 16, State 2, Line 2
The transaction ended in the trigger. The batch has been aborted._
Now switch to tempdb and see if you can create a table there
USE tempdb
GO
CREATE TABLE temp(id int)
_You must disable Trigger “ServerLevelsafety” to drop or alter tables!
Msg 3609, Level 16, State 2, Line 2
The transaction ended in the trigger. The batch has been aborted._
Nope, as you can see you can’t create a table in any database
Drop the trigger
DROP TRIGGER ServerLevelsafety ON ALL SERVER
Verify that you can indeed create a table again
USE tempdb
GO
CREATE TABLE temp(id int)
Let’s take a look at another example. What if you want to log anytime someone issues a DROP TABLE command in any database? You can easily accomplish that, and by using the EVENTDATA function you can capture the exact command that was used.
First create a new database with one table, this table will be used to store the messages from the trigger
CREATE DATABASE Logs
GO
USE Logs
GO
CREATE TABLE TriggerLog(DDL VARCHAR(300),DatabaseName VARCHAR(100),ExecutedBy VARCHAR(100), EventDate DATETIME)
GO
Create the following trigger
CREATE TRIGGER trDropTable
ON ALL SERVER -- A server level trigger
FOR DROP_TABLE --Event we want to capture
AS
INSERT tempdb..TriggerLog
SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)'), -- T-SQL command
EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(max)'), -- database name
COALESCE(SUSER_SNAME(),USER_NAME()),
GETDATE();
GO
The EVENT_INSTANCE/TSQLCommand/CommandText part of EVENTDATA will give us the actual command that was executed, the /EVENT_INSTANCE/DatabaseName part will give use the database name.
It is time to take it for a test, run the following block of code
USE Tempdb
GO
CREATE TABLE bla(id int)
GO
DROP TABLE bla
USE model
GO
CREATE TABLE bla(id int)
GO
DROP TABLE bla
Now if you look in your TriggerLog table, you will see 2 rows
SELECT * FROM Logs..TriggerLog
As you can see we have the two rows, since we grab the database name as well we can see what database it was run in in case you have the same table name in multiple databases
DDL DatabaseName ExecutedBy EventDate
-------------- ------------- --------------- ------------------------
DROP TABLE bla tempdb Denis-PCDenis 2011-12-12 20:47:23.607
DROP TABLE bla model Denis-PCDenis 2011-12-12 20:47:23.780
Also take a look at my post Use DDL Triggers To Track Table Changes to learn how you can capture all the DDL changes that were applied to a table over time….powerful stuff!
I just scratched the surface with what you can do with DDL triggers, below is a list of all the events that you can create your trigger for.
DDL Statements That Have Server or Database Scope
| DDL Events | <th>
DDL Events
</th>
<th>
DDL Events
</th>
</tr>
<tr>
<td>
<p>
CREATE_APPLICATION_ROLE (Applies to the CREATE APPLICATION ROLE statement and <strong>sp_addapprole</strong>. If a new schema is created, this event also triggers a CREATE_SCHEMA event.)
</p>
</td>
<td>
<p>
ALTER_APPLICATION_ROLE (Applies to the ALTER APPLICATION ROLE statement and <strong>sp_approlepassword</strong>.)
</p>
</td>
<td>
<p>
DROP_APPLICATION_ROLE (Applies to the DROP APPLICATION ROLE statement and <strong>sp_dropapprole</strong>.)
</p>
</td>
</tr>
<tr>
<td>
<p>
CREATE_ASSEMBLY
</p>
</td>
<td>
<p>
ALTER_ASSEMBLY
</p>
</td>
<td>
<p>
DROP_ASSEMBLY
</p>
</td>
</tr>
<tr>
<td>
<p>
CREATE_ASYMMETRIC_KEY
</p>
</td>
<td>
<p>
ALTER_ASYMMETRIC_KEY
</p>
</td>
<td>
<p>
DROP_ASYMMETRIC_KEY
</p>
</td>
</tr>
<tr>
<td>
<p>
ALTER_AUTHORIZATION
</p>
</td>
<td>
<p>
ALTER_AUTHORIZATION_DATABASE (Applies to the ALTER AUTHORIZATION statement when ON DATABASE is specified, and <strong>sp_changedbowner</strong>.)
</p>
</td>
<td>
</td>
</tr>
<tr>
<td>
<p>
CREATE_BROKER_PRIORITY
</p>
</td>
<td>
<p>
ALTER_BROKER_PRIORITY
</p>
</td>
<td>
<p>
DROP_BROKER_PRIORITY
</p>
</td>
</tr>
<tr>
<td>
<p>
CREATE_CERTIFICATE
</p>
</td>
<td>
<p>
ALTER_CERTIFICATE
</p>
</td>
<td>
<p>
DROP_CERTIFICATE
</p>
</td>
</tr>
<tr>
<td>
<p>
CREATE_CONTRACT
</p>
</td>
<td>
<p>
DROP_CONTRACT
</p>
</td>
<td>
</td>
</tr>
<tr>
<td>
<p>
ADD_COUNTER_SIGNATURE
</p>
</td>
<td>
<p>
DROP_COUNTER_SIGNATURE
</p>
</td>
<td>
</td>
</tr>
<tr>
<td>
<p>
CREATE_CREDENTIAL
</p>
</td>
<td>
<p>
ALTER_CREDENTIAL
</p>
</td>
<td>
<p>
DROP_CREDENTIAL
</p>
</td>
</tr>
<tr>
<td>
<p>
GRANT_DATABASE
</p>
</td>
<td>
<p>
DENY_DATABASE
</p>
</td>
<td>
<p>
REVOKE_DATABASE
</p>
</td>
</tr>
<tr>
<td>
<p>
CREATE_DATABASE_AUDIT_SPEFICIATION
</p>
</td>
<td>
<p>
ALTER_DATABASE_AUDIT_SPEFICIATION
</p>
</td>
<td>
<p>
DENY_DATABASE_AUDIT_SPEFICIATION
</p>
</td>
</tr>
<tr>
<td>
<p>
CREATE_DATABASE_ENCRYPTION_KEY
</p>
</td>
<td>
<p>
ALTER_DATABASE_ENCRYPTION_KEY
</p>
</td>
<td>
<p>
DROP_DATABASE_ENCRYPTION_KEY
</p>
</td>
</tr>
<tr>
<td>
<p>
CREATE_DEFAULT
</p>
</td>
<td>
<p>
DROP_DEFAULT
</p>
</td>
<td>
</td>
</tr>
<tr>
<td>
<p>
BIND_DEFAULT (Applies to <strong>sp_bindefault</strong>.)
</p>
</td>
<td>
<p>
UNBIND_DEFAULT (Applies to <strong>sp_unbindefault</strong>.)
</p>
</td>
<td>
</td>
</tr>
<tr>
<td>
<p>
CREATE_EVENT_NOTIFICATION
</p>
</td>
<td>
<p>
DROP_EVENT_NOTIFICATION
</p>
</td>
<td>
</td>
</tr>
<tr>
<td>
<p>
CREATE_EXTENDED_PROPERTY (Applies to <strong>sp_addextendedproperty</strong>.)
</p>
</td>
<td>
<p>
ALTER_EXTENDED_PROPERTY (Applies to <strong>sp_updateextendedproperty</strong>.)
</p>
</td>
<td>
<p>
DROP_EXTENDED_PROPERTY (Applies to <strong>sp_dropextendedproperty</strong>.)
</p>
</td>
</tr>
<tr>
<td>
<p>
CREATE_FULLTEXT_CATALOG (Applies to the CREATE FULLTEXT CATALOG statement and <strong>sp_fulltextcatalog</strong> when <span class="parameter">create</span> is specified.)
</p>
</td>
<td>
<p>
ALTER_FULLTEXT_CATALOG (Applies to the ALTER FULLTEXT CATALOG statement, <strong>sp_fulltextcatalog</strong> when <span class="parameter">start_incremental</span>, <span class="parameter">start_full</span>, <span class="parameter">Stop</span>, or <span class="parameter">Rebuild</span> is specified, and <strong>sp_fulltext_database</strong> when <span class="parameter">enable</span> is specified.)
</p>
</td>
<td>
<p>
DROP_FULLTEXT_CATALOG (Applies to the DROP FULLTEXT CATALOG statement and <strong>sp_fulltextcatalog</strong> when <span class="parameter">drop</span> is specified.)
</p>
</td>
</tr>
<tr>
<td>
<p>
CREATE_FULLTEXT_INDEX (Applies to the CREATE FULLTEXT INDEX statement and <strong>sp_fulltexttable</strong> when <span class="parameter">create</span> is specified.)
</p>
</td>
<td>
<p>
ALTER_FULLTEXT_INDEX (Applies to the ALTER FULLTEXT INDEX statement, <strong>sp_fulltextcatalog</strong> when <span class="parameter">start_full</span>, <span class="parameter">start_incremental</span>, or <span class="parameter">stop</span> is specified, <strong>sp_fulltext_column</strong>, and <strong>sp_fulltext_table</strong> when any action other than <span class="parameter">create</span> or <span class="parameter">drop</span> is specified.)
</p>
</td>
<td>
<p>
DROP_FULLTEXT_INDEX (Applies to the DROP FULLTEXT INDEX statement and <strong>sp_fulltexttable</strong> when <span class="parameter">drop</span> is specified.)
</p>
</td>
</tr>
<tr>
<td>
<p>
CREATE_FULLTEXT_STOPLIST
</p>
</td>
<td>
<p>
ALTER_FULLTEXT_STOPLIST
</p>
</td>
<td>
<p>
DROP_FULLTEXT_STOPLIST
</p>
</td>
</tr>
<tr>
<td>
<p>
CREATE_FUNCTION
</p>
</td>
<td>
<p>
ALTER_FUNCTION
</p>
</td>
<td>
<p>
DROP_FUNCTION
</p>
</td>
</tr>
<tr>
<td>
<p>
CREATE_INDEX
</p>
</td>
<td>
<p>
ALTER_INDEX (Applies to the ALTER INDEX statement and <strong>sp_indexoption</strong>.)
</p>
</td>
<td>
<p>
DROP_INDEX
</p>
</td>
</tr>
<tr>
<td>
<p>
CREATE_MASTER_KEY
</p>
</td>
<td>
<p>
ALTER_MASTER_KEY
</p>
</td>
<td>
<p>
DROP_MASTER_KEY
</p>
</td>
</tr>
<tr>
<td>
<p>
CREATE_MESSAGE_TYPE
</p>
</td>
<td>
<p>
ALTER_MESSAGE_TYPE
</p>
</td>
<td>
<p>
DROP_MESSAGE_TYPE
</p>
</td>
</tr>
<tr>
<td>
<p>
CREATE_PARTITION_FUNCTION
</p>
</td>
<td>
<p>
ALTER_PARTITION_FUNCTION
</p>
</td>
<td>
<p>
DROP_PARTITION_FUNCTION
</p>
</td>
</tr>
<tr>
<td>
<p>
CREATE_PARTITION_SCHEME
</p>
</td>
<td>
<p>
ALTER_PARTITION_SCHEME
</p>
</td>
<td>
<p>
DROP_PARTITION_SCHEME
</p>
</td>
</tr>
<tr>
<td>
<p>
CREATE_PLAN_GUIDE (Applies to <strong>sp_create_plan_guide</strong>.)
</p>
</td>
<td>
<p>
ALTER_PLAN_GUIDE (Applies to <strong>sp_control_plan_guide</strong> when ENABLE, ENABLE ALL, DISABLE, or DISABLE ALL is specified.)
</p>
</td>
<td>
<p>
DROP_PLAN_GUIDE (Applies to <strong>sp_control_plan_guide</strong> when DROP or DROP ALL is specified.)
</p>
</td>
</tr>
<tr>
<td>
<p>
CREATE_PROCEDURE
</p>
</td>
<td>
<p>
ALTER_PROCEDURE (Applies to the ALTER PROCEDURE statement and <strong>sp_procoption</strong>.)
</p>
</td>
<td>
<p>
DROP_PROCEDURE
</p>
</td>
</tr>
<tr>
<td>
<p>
CREATE_QUEUE
</p>
</td>
<td>
<p>
ALTER_QUEUE
</p>
</td>
<td>
<p>
DROP_QUEUE
</p>
</td>
</tr>
<tr>
<td>
<p>
CREATE_REMOTE_SERVICE_BINDING
</p>
</td>
<td>
<p>
ALTER_REMOTE_SERVICE_BINDING
</p>
</td>
<td>
<p>
DROP_REMOTE_SERVICE_BINDING
</p>
</td>
</tr>
<tr>
<td>
<p>
CREATE_SPATIAL_INDEX
</p>
</td>
<td>
</td>
<td>
</td>
</tr>
<tr>
<td>
<p>
RENAME (Applies to <strong>sp_rename</strong>)
</p>
</td>
<td>
</td>
<td>
</td>
</tr>
<tr>
<td>
<p>
CREATE_ROLE (Applies to the CREATE ROLE statement, <strong>sp_addrole</strong>, and <strong>sp_addgroup</strong>.)
</p>
</td>
<td>
<p>
ALTER_ROLE
</p>
</td>
<td>
<p>
DROP_ROLE (Applies to the DROP ROLE statement, <strong>sp_droprole</strong>, and <strong>sp_dropgroup</strong>.)
</p>
</td>
</tr>
<tr>
<td>
<p>
ADD_ROLE_MEMBER
</p>
</td>
<td>
<p>
DROP_ROLE_MEMBER
</p>
</td>
<td>
</td>
</tr>
<tr>
<td>
<p>
CREATE_ROUTE
</p>
</td>
<td>
<p>
ALTER_ROUTE
</p>
</td>
<td>
<p>
DROP_ROUTE
</p>
</td>
</tr>
<tr>
<td>
<p>
CREATE_RULE
</p>
</td>
<td>
<p>
DROP_RULE
</p>
</td>
<td>
</td>
</tr>
<tr>
<td>
<p>
BIND_RULE (Applies to <strong>sp_bindrule</strong>.)
</p>
</td>
<td>
<p>
UNBIND_RULE (Applies to <strong>sp_unbindrule</strong>.)
</p>
</td>
<td>
</td>
</tr>
<tr>
<td>
<p>
CREATE_SCHEMA (Applies to the CREATE SCHEMA statement, <strong>sp_addrole</strong>, <strong>sp_adduser</strong>, <strong>sp_addgroup</strong>, and <strong>sp_grantdbaccess</strong>.)
</p>
</td>
<td>
<p>
ALTER_SCHEMA (Applies to the ALTER SCHEMA statement and <strong>sp_changeobjectowner</strong>.)
</p>
</td>
<td>
<p>
DROP_SCHEMA
</p>
</td>
</tr>
<tr>
<td>
<p>
CREATE_SERVICE
</p>
</td>
<td>
<p>
ALTER_SERVICE
</p>
</td>
<td>
<p>
DROP_SERVICE
</p>
</td>
</tr>
<tr>
<td>
<p>
ALTER_SERVICE_MASTER_KEY
</p>
</td>
<td>
<p>
BACKUP_SERVICE_MASTER_KEY
</p>
</td>
<td>
<p>
RESTORE_SERVICE_MASTER_KEY
</p>
</td>
</tr>
<tr>
<td>
<p>
ADD_SIGNATURE
</p>
</td>
<td>
<p>
DROP_SIGNATURE
</p>
</td>
<td>
</td>
</tr>
<tr>
<td>
<p>
CREATE_SPATIAL_INDEX
</p>
</td>
<td>
<p>
ALTER_INDEX can be used for spatial indexes.
</p>
</td>
<td>
<p>
DROP_INDEX can be used for spatial indexes.
</p>
</td>
</tr>
<tr>
<td>
<p>
CREATE_STATISTICS
</p>
</td>
<td>
<p>
DROP_STATISTICS
</p>
</td>
<td>
<p>
UPDATE_STATISTICS
</p>
</td>
</tr>
<tr>
<td>
<p>
CREATE_SYMMETRIC_KEY
</p>
</td>
<td>
<p>
ALTER_SYMMETRIC_KEY
</p>
</td>
<td>
<p>
DROP_SYMMETRIC_KEY
</p>
</td>
</tr>
<tr>
<td>
<p>
CREATE_SYNONYM
</p>
</td>
<td>
<p>
DROP_SYNONYM
</p>
</td>
<td>
</td>
</tr>
<tr>
<td>
<p>
CREATE_TABLE
</p>
</td>
<td>
<p>
ALTER_TABLE (Applies to the ALTER TABLE statement and <strong>sp_tableoption</strong>.)
</p>
</td>
<td>
<p>
DROP_TABLE
</p>
</td>
</tr>
<tr>
<td>
<p>
CREATE_TRIGGER
</p>
</td>
<td>
<p>
ALTER_TRIGGER (Applies to the ALTER TRIGGER statement and <strong>sp_settriggerorder</strong>.)
</p>
</td>
<td>
<p>
DROP_TRIGGER
</p>
</td>
</tr>
<tr>
<td>
<p>
CREATE_TYPE (Applies to the CREATE TYPE statement and <strong>sp_addtype</strong>.)
</p>
</td>
<td>
<p>
DROP_TYPE (Applies to the DROP TYPE statement and <strong>sp_droptype</strong>.)
</p>
</td>
<td>
</td>
</tr>
<tr>
<td>
<p>
CREATE_USER (Applies to the CREATE USER statement, <strong>sp_adduser</strong>, and <strong>sp_grantdbaccess</strong>.)
</p>
</td>
<td>
<p>
ALTER_USER (Applies to ALTER USER statement and <strong>sp_change_users_login</strong>.)
</p>
</td>
<td>
<p>
DROP_USER (Applies to the DROP USER statement, <strong>sp_dropuser</strong>, and <strong>sp_revokedbaccess</strong>.)
</p>
</td>
</tr>
<tr>
<td>
<p>
CREATE_VIEW
</p>
</td>
<td>
<p>
ALTER_VIEW
</p>
</td>
<td>
<p>
DROP_VIEW
</p>
</td>
</tr>
<tr>
<td>
<p>
CREATE_XML_INDEX
</p>
</td>
<td>
<p>
ALTER_INDEX can be used for XML indexes.
</p>
</td>
<td>
<p>
DROP_INDEX can be used for XML indexes.
</p>
</td>
</tr>
<tr>
<td>
<p>
CREATE_XML_SCHEMA_COLLECTION
</p>
</td>
<td>
<p>
ALTER_XML_SCHEMA_COLLECTION
</p>
</td>
<td>
<p>
DROP_XML_SCHEMA_COLLECTION
</p>
</td>
</tr>
|---|
DDL Statements That Have Server Scope
| DDL Events | <th>
DDL Events
</th>
<th>
DDL Events
</th>
</tr>
<tr>
<td>
<p>
ALTER_AUTHORIZATION_SERVER
</p>
</td>
<td>
<p>
ALTER_SERVER_CONFIGURATION
</p>
</td>
<td>
<p>
ALTER_INSTANCE (Applies to <strong>sp_configure</strong> and <strong>sp_addserver</strong> when a local server instance is specified.)
</p>
</td>
</tr>
<tr>
<td>
<p>
CREATE_CREDENTIAL
</p>
</td>
<td>
<p>
ALTER_CREDENTIAL
</p>
</td>
<td>
<p>
DROP_CREDENTIAL
</p>
</td>
</tr>
<tr>
<td>
<p>
CREATE_CRYPTOGRAPHIC_PROVIDER
</p>
</td>
<td>
<p>
ALTER_CRYPTOGRAPHIC_PROVIDER
</p>
</td>
<td>
<p>
DROP_CRYPTOGRAPHIC_PROVIDER
</p>
</td>
</tr>
<tr>
<td>
<p>
CREATE_DATABASE
</p>
</td>
<td>
<p>
ALTER_DATABASE (Applies to the ALTER DATABASE statement and <strong>sp_fulltext_database</strong>.)
</p>
</td>
<td>
<p>
DROP_DATABASE
</p>
</td>
</tr>
<tr>
<td>
<p>
CREATE_ENDPOINT
</p>
</td>
<td>
<p>
ALTER_ENDPOINT
</p>
</td>
<td>
<p>
DROP_ENDPOINT
</p>
</td>
</tr>
<tr>
<td>
<p>
CREATE_EVENT_SESSION
</p>
</td>
<td>
<p>
ALTER_EVENT_SESSION
</p>
</td>
<td>
<p>
DROP_EVENT_SESSION
</p>
</td>
</tr>
<tr>
<td>
<p>
CREATE_EXTENDED_PROCEDURE (Applies to <strong>sp_addextendedproc</strong>.)
</p>
</td>
<td>
<p>
DROP_EXTENDED_PROCEDURE (Applies to <strong>sp_dropextendedproc</strong>.)
</p>
</td>
<td>
</td>
</tr>
<tr>
<td>
<p>
CREATE_LINKED_SERVER (Applies to <strong>sp_addlinkedserver</strong>.)
</p>
</td>
<td>
<p>
ALTER_LINKED_SERVER (Applies to <strong>sp_serveroption</strong>.)
</p>
</td>
<td>
<p>
DROP_LINKED_SERVER (Applies to <strong>sp_dropserver</strong> when a linked server is specified.)
</p>
</td>
</tr>
<tr>
<td>
<p>
CREATE_LINKED_SERVER_LOGIN (Applies to <strong>sp_addlinkedsrvlogin</strong>.)
</p>
</td>
<td>
<p>
DROP_LINKED_SERVER_LOGIN (Applies to <strong>sp_droplinkedsrvlogin</strong>.)
</p>
</td>
<td>
</td>
</tr>
<tr>
<td>
<p>
CREATE_LOGIN (Applies to the CREATE LOGIN statement, <strong>sp_addlogin</strong>, <strong>sp_grantlogin</strong>, <strong>xp_grantlogin</strong>, and <strong>sp_denylogin</strong> when used on a nonexistent login that must be implicitly created.)
</p>
</td>
<td>
<p>
ALTER_LOGIN (Applies to the ALTER LOGIN statement, <strong>sp_defaultdb</strong>, <strong>sp_defaultlanguage</strong>, <strong>sp_password</strong>, and <strong>sp_change_users_login</strong> when <span class="parameter">Auto_Fix</span> is specified.)
</p>
</td>
<td>
<p>
DROP_LOGIN (Applies to the DROP LOGIN statement, <strong>sp_droplogin</strong>, <strong>sp_revokelogin</strong>, and <strong>xp_revokelogin</strong>.)
</p>
</td>
</tr>
<tr>
<td>
<p>
CREATE_MESSAGE (Applies to <strong>sp_addmessage</strong>.)
</p>
</td>
<td>
<p>
ALTER_MESSAGE (Applies to <strong>sp_altermessage</strong>.)
</p>
</td>
<td>
<p>
DROP_MESSAGE (Applies to <strong>sp_dropmessage</strong>.)
</p>
</td>
</tr>
<tr>
<td>
<p>
CREATE_REMOTE_SERVER (Applies to <strong>sp_addserver</strong>.)
</p>
</td>
<td>
<p>
ALTER_REMOTE_SERVER (Applies to <strong>sp_setnetname</strong>.)
</p>
</td>
<td>
<p>
DROP_REMOTE_SERVER (Applies to <strong>sp_dropserver</strong> when a remote server is specified.)
</p>
</td>
</tr>
<tr>
<td>
<p>
GRANT_SERVER
</p>
</td>
<td>
<p>
DENY_SERVER
</p>
</td>
<td>
<p>
REVOKE_SERVER
</p>
</td>
</tr>
<tr>
<td>
<p>
ADD_SERVER_ROLE_MEMBER
</p>
</td>
<td>
<p>
DROP_SERVER_ROLE_MEMBER
</p>
</td>
<td>
</td>
</tr>
<tr>
<td>
<p>
CREATE_SERVER_AUDIT
</p>
</td>
<td>
<p>
ALTER_SERVER_AUDIT
</p>
</td>
<td>
<p>
DROP_SERVER_AUDIT
</p>
</td>
</tr>
<tr>
<td>
<p>
CREATE_SERVER_AUDIT_SPECIFICATION
</p>
</td>
<td>
<p>
ALTER_SERVER_AUDIT_SPECIFICATION
</p>
</td>
<td>
<p>
DROP_SERVER_AUDIT_SPECIFICATION
</p>
</td>
</tr>
<tr>
<td>
<p>
CREATE_RESOURCE_POOL
</p>
</td>
<td>
<p>
ALTER_RESOURCE_POOL
</p>
</td>
<td>
<p>
DROP_RESOURCE_POOL
</p>
</td>
</tr>
<tr>
<td>
<p>
CREATE_WORKLOAD_GROUP
</p>
</td>
<td>
<p>
CREATE_WORKLOAD_GROUP
</p>
</td>
<td>
<p>
CREATE_WORKLOAD_GROUP
</p>
</td>
</tr>
|---|
Come back tomorrow for the next post in this series
Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
