SQL Server DDL triggers (Trigger)-Create a server-level DDL trigger

SQL Server DDL triggers (Trigger)-Create a server-level DDL trigger

If you create a server-level DDL trigger, you can track server-level events by changing the previous on database to the on all server, using the same principle as the DDL triggers at the DB level, which differ only in the events that are tracked.

CREATE TRIGGER Ddl_trig_loginon all serverfor ddl_login_eventsasprint n ' ALTER LOGIN EVENT ' SELECT EVENTDATA (). Value (' (/ Event_instance/tsqlcommand/commandtext) [1] ', ' nvarchar (max) ')

Trace Ddl_login_events type event, as long as there is a new, modify, delete login account of the event occurs, the trigger executes the program, also can use the eventdata () function to obtain the execution of the trigger related system information. The following statements can be used to test:

CREATE LOGIN test with password= ' Mpdfzh7 '

Similarly, if you want to remove a server-level DDL trigger that is similar to a database-level DDL trigger, just change to on all server, refer to the following statement:

DROP TRIGGER Ddl_trig_loginon All SERVER

Triggers that are raised by logon events are also available when you want to prohibit a person or an application from logging on to SQL Server. For example, after SQL Server 2008, the T-SQL IntelliSense functionality provided by Management Studio occupies a small amount of CPU and memory resources on the server. If the server side of the data object is not much, the impact is small, but if there are many objects, while online editing users are also many, it will consume large resources. Triggers that you can throw through the logon event. Do not allow the SQL Server Management Studio utility to query connection logins for data required by T-SQL IntelliSense.

First, the SQL Server Profiler tool program observes what application name is used to log on to SQL when Management Studio's T-SQL Editor wants to get a server-side data object to provide developer T-SQL IntelliSense Server, the recording results are as follows. (Note: The author uses SQL Server 2012 recording, does not capture related events for the app, the following tests are completed under SQL Server R2, SQL Server R2 Management Studio's T-SQL editing environment first queries the server-side object and then provides IntelliSense functionality when T-SQL is written.

650) this.width=650; "title=" clip_image001 "style=" Border-top:0px;border-right:0px;border-bottom:0px;border-left : 0px; "alt=" clip_image001 "src=" http://s3.51cto.com/wyfs02/M01/57/BF/wKiom1SjyC7jZ0D2AAULgdVvlAc290.jpg "border=" 0 "height=" 325 "/>

For logon events, after you create a simple server-level DDL trigger, use the APP_NAME system function to determine the name of the logged-on application, similar to "Microsoft SQL Server Management studio-transact-sql IntelliSense, it is not possible to connect through the rollback command, the code is as follows.

CREATE TRIGGER Intellisense_connection_limit_triggeron all SERVER for logonasbeginif app_name () like '% Microsoft SQL Serv Er Management studio-transact-sql IntelliSense% ' ROLLBACK; END;

After execution, the IntelliSense functionality provided by SQL Server R2 Management Studio's T-SQL Editor is invalidated.

Using the same technique, you can also write a DDL trigger, and when the server is connected too much, you are not allowed to create new, unimportant connections to maintain the stability and efficiency of the database, and allow the existing connections to complete the work. Lest the system be extremely busy, add another connection, like the last straw, crushing the other business that has been running for a while. However, if you do, you may have to quarrel with the developers for server management.

In addition, when the "policies" mechanism provided by SQL Server R2 is set, and its "Evaluation Mode" is "on change-Prevent", SQL Server is also a DDL trigger that takes advantage of the server several times, after the event occurs, Immediately evaluates whether the action content conforms to the previously specified policy, and rollback the original condition if the policy is violated.

650) this.width=650; "title=" clip_image002 "style=" Border-top:0px;border-right:0px;border-bottom:0px;border-left : 0px; "alt=" clip_image002 "src=" http://s3.51cto.com/wyfs02/M02/57/BF/wKiom1SjyC-zjyPeAAO-xiWUyK4690.jpg "border=" 0 "height=" 606 "/>

650) this.width=650; "title=" clip_image003 "style=" Border-top:0px;border-right:0px;border-bottom:0px;border-left : 0px; "alt=" clip_image003 "src=" http://s3.51cto.com/wyfs02/M00/57/BF/wKiom1SjyDDRUAEwAADc_o2D930759.jpg "border=" 0 "height=" 278 "/>

When "policies" is set, "Evaluation Mode" is "on change--prevent" and "Enabled" to automatically create a server-level DDL trigger. If you actually observe the trigger, you can see something like this.

CREATE TRIGGER [Syspolicy_server_trigger] on all serverwith EXECUTE as ' # #MS_PolicyEventProcessingLogin # # ' for Alter_ Authorization_database,alter_procedure,alter_schema,create_procedure,renameasbegindeclare @event_data XmlSELECT @ Event_data = EVENTDATA () EXEC [msdb]. [dbo]. [Sp_syspolicy_dispatch_event] @event_data = @event_data, @synchronous = 1END

This article is from the SQL Server deep dives blog, so be sure to keep this source http://ultrasql.blog.51cto.com/9591438/1598188

SQL Server DDL triggers (Trigger)-Create a server-level DDL trigger