Creating a DDL Trigger that Audits Server-Level Events : Trigger on server « Trigger « SQL Server / T-SQL Tutorial

1. SQL Server / T-SQL Tutorial
2. Trigger
3. Trigger on server

4>
5> USE master
6> GO
Changed database context to 'master'.
1> -- Disallow new Logins on the SQL instance
2> CREATE TRIGGER trg_RestrictNewLogins
3> ON ALL SERVER
4> FOR CREATE_LOGIN
5> AS
6> PRINT 'No login creations without DBA involvement.'
7> ROLLBACK
8> GO

1>
2> CREATE LOGIN JoeS WITH PASSWORD = 'Password'
3> GO
Msg 15118, Level 16, State 1, Server BCE67B1242DE45A\SQLEXPRESS, Line 2
Password validation failed. The password does not meet Windows policy requirements because it is not complex enough.
1>
2> drop trigger trg_RestrictNewLogins
3> GO
Msg 3701, Level 11, State 5, Server BCE67B1242DE45A\SQLEXPRESS, Line 2
Cannot drop the trigger 'trg_RestrictNewLogins', because it does not exist or you do not have permission.