By Paul Cannon on Nov 21, 2013 5:43:00 PM
The following is a repost of an article featured in the Orbis Software Knowledgebase.
The ‘MS SQL Server Trigger’ tool allows you to create and manage SQL triggers through an easy to use interface. Behind the scenes the tool builds CREATE TRIGGER statements. The CREATE TRIGGER statements are saved to the TaskCentre store for later processing by the ‘TaskCentre SQL agent’ service.
At this point the CREATE TRIGGER statements are just literal text in the TaskCentre store.
The ‘TaskCentre SQL agent’ service polls the TaskCentre store, copies the CREATE TRIGGER statements, wraps them in OLEDB code, connects to the SQL server and finally creates the triggers on the database.
Now that we have the triggers created on the database, how do these cause a task to be run?
When an UPDATE/INSERT/DELETE takes place on the table the trigger is on, it creates an instance of the SQL Connector class (This is a TaskCentre component) to communicate with TaskCentre server on TCP port 4222 passing it the id of the task to be run, along with any other parameters set up by the user when they created the trigger within the tool. This information is held in a queue in the TaskCentre server which in turn runs the task.
The following three prerequistes are required in order for the trigger to cause a task within TaskCentre to run.
Related: TaskCentre System Requirements
1. Enable OLE Automation
SQL SERVER 2005
- Start -> All Programs -> Microsoft SQL Server 2005 -> Configuration Tools -> SQL Server Surface Area Configuration. Within the “SQL Server Surface Area Configuration” tool select “Surface Area Configuration for Features”.
- Tick the “OLE Automation” property checkbox.
SQL SERVER 2008/2012
- From within SQL Server Management Studio right click the server node and choose Facets from the context menu.
- Within the View Facets window select “Surface Area Configuration” from the Facets drop down list.
- In the list of properties for this facet set “OleAutomationEnabled” to “True”.
2. Set DCOM permission for the SQL connector class
• Start -> Run and type DCOMCNFG and hit Enter to open Component Services.
• Under the Computers folder expand the DCOM config branch and locate the iwsqlea component.
• For the ‘iwsqlea’ component give ‘Local Launch’, ‘Local Activation’ and ‘Local Access’ permissions to the account that the SQL server service runs under.
Normally SQL server 2005/2008/2010 runs under the NETWORK SERVICE account. However this can be set differently so you should check which account the SQL server is running under so that you are applying the DCOM permissions to the correct account.
3. Apply Execute permission on the extended stored procedures in the master database
- From the object explorer within SQL Server Management Studio select: Database -> System Databases -> Master -> Programmability -> Extended Stored Procedures -> System Extended Stored Procedures.
- Grant Execute permission on the following four stored procedures:
You can grant Execute permission to any of the following:
A SQL server role (Such as the 'public' role)
A SQL server account
A Windows group (Such as ‘Domain users’)
A Windows account
Security Considerations When Using the SQL Trigger
When using SQL server 2005 or 2008 and not logging in using a server administrator account, there are 4 user permissions necessary to browse databases and create triggers:
- To browse the server, the login needs to be granted the VIEW ANY DEFINITIONS permission.
- To view a list of databases, for each database to be viewed there must be a database user created and mapped to the login user.
- To view tables in a database, the user must be granted the SELECT permission on the schema which owns the table.
- To actually create the trigger on a table, the user needs the ALTER permission on the schema that owns the table.
USE [master]GRANT VIEW ANY DEFINITION TO [user]
USE [databasename]CREATE USER [username] FOR LOGIN [username]GO
- servername->Security->Logins , doubleclick the login
- choose the user mapping page, tick the "Map" box for all databases the login user needs to see.
USE[databasename]GRANT SELECT,ALTER ON SCHEMA::[schemaname] TO test1
- doubleclick the schema
- choose the Permissions page
- click the search button and add the user to the "Users or roles" list
- Tick the "Grant" box for the "Alter" and "Select" permissions.