Required permissions to create SQL Server triggers, and have the triggers run a TaskCentre task

Permissions to Create or Delete a trigger

Before you can work with triggers from the Microsoft SQL Server Trigger tool, you must have the following permissions to create or delete the trigger:

ActionRequired Permission
Browse databases on the SQL instance from the Microsoft SQL Server Trigger toolServer level CONNECT SQL – Grants the ability to connect to the database.

Server level VIEW ANY DEFINITION – Required in order to view object definitions.

Create or Delete triggers on a table from the Microsoft SQL Server Trigger toolTable level SELECT – Required in order to select an object for ALTER.

Table level ALTER – Grants the ability to add or remove trigger objects.

Permissions to run a task when the trigger responds to an event

Before an application can update a table, which has a trigger, that runs a task, the account that the application uses to connect to the SQL instance with must have the following permissions:

ActionRequired Permission
Allow OLE Automation objects to be referenced in Transact-SQL triggers.EXECUTE on the following extended stored procedures in the master database:
sp_OACREATE
sp_OAMETHOD
sp_OADESTROY
Allow events to be logged to the Windows application event log from Transact-SQL triggers.EXECUTE on the following extended stored procedure in the master database:
xp_LogEvent
Enable SQL Server OLE Automation for the specified SQL instance.Within the Configurator ensure that ‘SQL Server OLE Automation’ has a green tick. (see below screenshot)
DCOM: Give the account that the SQL Server instance runs under; Local Launch, Local Activation and Local Access to the SQL Agent component.Within the Configurator ensure ‘Agent Permissions’ has a green tick. (see below screenshot)

Screenshot showing the TaskCentre Configurator SQL Server OLE Automation and DCOM Agent Permissions:

OLE Config

Rights to Stored Procedures – Example Script

To enable EXECUTE on the extended stored procedures of the master database, as an example the following SQL script can be used:

use [master]
GO
GRANT EXECUTE ON [sys].[sp_OACreate] TO [AppAccount]
GRANT EXECUTE ON [sys].[sp_OADestroy] TO [AppAccount]
GRANT EXECUTE ON [sys].[sp_OAMethod] TO [AppAccount]
GRANT EXECUTE ON [sys].[xp_LogEvent] TO [AppAccount]
GO

Where [AppAccount] you would specify the name of the account being used by the business application to access the SQL instance, this SQL account is essentially the one being used to trigger the task.

Troubleshooting Guide

If you encounter any problems please click the relevant link below:

Trigger will not run: Trigger is created but unable to modify database table

Trigger will not run: Trigger is created and can modify database table but task is not triggered

Configurator not detecting OLE Automation flag when enabled from the database; a red cross still displays