How to: Prerequisites for running SQL Triggers

The following is a repost of an article featured in the Orbis Software Knowledgebase.

Summary

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.

Prerequisites

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:
    sp_OACreate
    sp_OAMethod
    sp_OADestroy
    xp_logevent

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:

  1. To browse the server, the login needs to be granted the VIEW ANY DEFINITIONS permission.
  2. 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.
  3. To view tables in a database, the user must be granted the SELECT permission on the schema which owns the table.
  4. To actually create the trigger on a table, the user needs the ALTER permission on the schema that owns the table.
To grant VIEW ANY DEFINITIONS, execute the following SQL:
 
USE [master]
GRANT VIEW ANY DEFINITION TO [user]
 
To create and map a database user for a login, execute the following SQL:
 
USE [databasename]
CREATE USER [username] FOR LOGIN [username]
GO
 
Or, from SQL server management studio, in the tree browser:
  • servername->Security->Logins , doubleclick the login
  • choose the user mapping page, tick the "Map" box for all databases the login user needs to see.
To grant the SELECT and ALTER permissions for a user on a schema , execute the following SQL:
 
USE[databasename]
GRANT SELECT,ALTER ON SCHEMA::[schemaname] TO test1
 
Or, from sql server management studio, in the tree browser:
  • servername->Databases->databasename->Security->Schemas
  • 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. 
comments
0