DDL triggers enable us to audit DDL changes but there are a few missing events, design decisions and installation complications.
In a previous tip, Granting limited permissions to create views in another schema in SQL Server, I showed how to use the CREATE VIEW permission and the ALTER SCHEMA permission to allow users to create new views using the same schema as the tables. However, I have found that the users can alter and drop tables in this schema which is beyond what I want them to do. A DENY CREATE TABLE does not work, so what can I do to prevent them from touching the tables in this way? In this tip I will show you how you can use a DDL trigger to prevent this unneeded access.
SQL Server login monitoring and access control are important elements of SQL database security. Learn how DDL triggers can help alert you to security issues.
Auditing and monitoring activity in your databases is becoming more and more important all the time. New author David Dye brings us a short introduction to using DDL triggers to help.