Figure 6-19 Database-level and database security DDL events in Visual Studio .NET

Generator QR Code JIS X 0510 in Visual Studio .NET Figure 6-19 Database-level and database security DDL events

DDL_DATABASE SECURITY_EVENTS DDL_CERTIFICATE_EVENTS (CREATE CERTIFICATE, ALTER CERTIFICATE, DROP CERTIFICATE) DDL_USER_EVENTS (CREATE USER, ALTER USER, DROP USER)

DDL_ROLE_EVENTS (CREATE ROLE, ALTER ROLE, DROP ROLE) DDL_APPLICATION_ROLE_EVENTS (CREATE APPROLE, ALTER APPROLE, DROP APPROLE)

DDL_SCHEMA_EVENTS (CREATE SCHEMA, ALTER SCHEMA, DROP SCHEMA)

DDL_GDR_DATABASE_EVENTS (GRANT DATABASE, DENY DATABASE, REVOKE DATABASE)

DDL_AUTHORIZATION_DATABASE_EVENTS (ALTER AUTHORIZATION DATABASE)

DDL_MESSAGE_TYPE_EVENTS (CREATE MSGTYPE, ALTER MSGTYPE, DROP MSGTYPE) DDL_CONTRACT_EVENTS (CREATE CONTRACT, ALTER CONTRACT, DROP CONTRACT)

DDL_QUEUE_EVENTS (CREATE QUEUE, ALTER QUEUE, DROP QUEUE)

DDL_SERVICE_EVENTS (CREATE SERVICE, ALTER SERVICE, DROP SERVICE)

DDL_ROUTE_EVENTS (CREATE ROUTE, ALTER ROUTE, DROP ROUTE)

(CREATE REMOTE_SERVICE_BINDING, ALTER REMOTE_SERVICE_BINDING, DROP REMOTE_SERVICE_BINDING)

DDL_DATABASE_LEVEL_EVENTS DDL_XML_SCHEMA_COLLECTION_EVENTS (CREATE XML SCHEMA COLLECTION, ALTER XML SCHEMA COLLECTION, DROP XML SCHEMA COLLECTION)

DDL_PARTITION_FUNCTION_EVENTS (CREATE PARTITION FUNCTION, ALTER PARTITION FUNCTION, DROP PARTITION FUNCTION)

DDL_PARTITION_SCHEME_EVENTS (CREATE PARTITION SCHEME, ALTER PARTITION SCHEME, DROP PARTITION SCHEME)

One of the primary purposes of a DDL trigger is to use it for auditing changes to either the server or a database Change management has become an important concept to grasp and implement within the IT community The Gartner Group has released information from studies that indicate that as much as 80 percent of outages are due to people and processes Only about 20 percent of outage durations are due to actual hardware failure The other 80 percent is attributable to things like unauthorized changes, changes being made without following procedures, lack of procedures, human error, and other operator error or process failures With change management policies and procedures in place, when a need for a change occurs, instead of just implementing it, the process is slowed down so the impact of the change can be examined For example, modifying the schema of a table by deleting what appears to be an unused column, or renaming a column so it is more intuitive, may seem like a harmless change However, this change will adversely affect any other database objects referencing that column A critical stored procedure or a frequently queried VIEW could suddenly stop working A change management policy would allow key players to examine the change using tools such as sp_depends to identify any object dependencies In addition to change management policies and procedures, an auditing strategy can be implemented that records the changes, including who did it and when they did it

By creating an ALTER_TABLE trigger, you could catch any statements that altered a table and record the auditing information Once the change is caught, the trigger can be programmed to record it in a table within the database, record it in a table in a special auditing database, use tools like Database Mail to immediately send an e-mail to the appropriate personnel, or even roll back the change EXAM TIP Consider DDL triggers to audit schema changes to a database, or changes at the server level A DDL trigger can catch the change and then record it (such as in a database table), notify DBAs with Database Mail, and/or roll back the change In the following exercise, we ll create a DDL trigger that will prevent any changes to tables by monitoring the ALTER TABLE statement As with any trigger, you can change the content of the trigger to do what you want Exercise 66: Create a DDL Trigger to Prevent Changes to Tables 1 Open a New Query window in SSMS 2 Use the following query to create a database-scoped trigger to monitor for the ALTER TABLE statement The trigger will then give an error and roll back the statement

USE 6; GO CREATE TRIGGER NoTableChanges ON DATABASE FOR ALTER_TABLE AS RAISERROR ('Tables cannot be altered in this database', 16, 1) ROLLBACK;

3 Try to alter a table within the database with this statement:

ALTER TABLE dboZipState ADD NewColumn varChar(20) NULL;

The ALTER TABLE statement fails and is rolled back by the trigger In the following exercise, we ll create a DDL trigger that will monitor for the creation of databases and log the SQL script used to create the database in the application log We could just as easily create an audit database and log the creation of databases in a table within the audit database Exercise 67: Create a DDL Trigger to Monitor the Creation of Databases 1 Open a New Query window in SSMS 2 Use the following query to create a server-scoped trigger to monitor for the CREATE DATABASE statement The trigger gives feedback, saying the event occurred, and then logs it into the application log