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:
| Action | Required Permission |
| Browse databases on the SQL instance from the Microsoft SQL Server Trigger tool | Server 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 tool | Table 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:
| Action | Required 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:
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
