Error in Trigger

Question

Error in Trigger

lmarkum

SSCertifiable

Points: 7985
More actions
September 15, 2017 at 11:34 am

#340056

I'm trying to devise a way to run a PowerShell script from a job when changes are made to project deployment info in SSISDB. This is because we have SSISDB on two stand alone nodes that are part of an AG. SSISDB isn't in the AG as this is SQL Server 2014 and that isn't really supported. When the Devs deploy a project there is info updated in [SSISDB].[internal].[projects]. I have a simple trigger on that table that calls msdb.dbo.sp_start_job with a job name after an INSERT, UPDATE, DELETE and the job runs PowerShell to copy the Catalog to the other node so they stay in synch. When I tested the trigger concept on an initial test environment with a different database and scenario it worked fine. What I'm doing now is on the test AG machines trying to deploy an SSIS project to SSISDB and the deploy is failing because the trigger throws an error that there are insufficient permissions to run EXEC msdb.dbo.sp_start_job @job_name = 'SSISDB Catalog Synch'. How do I figure out what the execution context is so I can resolve the permissions issue?
Here is my trigger.
ALTER TRIGGER [internal].[SSISDBCatalogSynch] ON [SSISDB].[internal].[projects] AFTER INSERT, UPDATE, DELETE
AS
EXEC msdb.dbo.sp_start_job @job_name = 'SSISDB Catalog Synch'

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

have you set a proxy up on the job that is going to do the work?
that's pretty essential for anything that touches stuff outside of SQL server, like command line, powershell, unc paths, other server.s

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

lmarkum SSCertifiable Points: 7985 More actions · Answer 2

Lowell, thanks for the reply. The process isn't even getting far enough to start the job that executes a PowerShell command inline in the job. It gets an error when it tries to execute a proc in msdb. I don't have a proxy being used though and I didn't have one in the test environment when this worked.

Lowell SSC Guru Points: 323504 More actions · Answer 3

gotcha, i think the issue is the cross database query call.

then normal individual who enters the data is not in the [msdb] SQLAgentOperatorRole or SQLAgentUserRole, which arethe roles that are allowed to start jobs, right?.

i think the fix is to either sign the trigger with a certificate, so it uses that permissions instead, or maybe adding EXECUTE AS OWNER and see if that resolves the permissions issue.
it's the same issue you see when you try to send an email inside a procedure or trigger, and the calling user is not in the DatabaseMailUserRole

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lowell SSC Guru Points: 323504 More actions · Answer 4

i would think getting rid of the trigger, and creating a simple TSQL job that runs every hour would be better and easier.

a script like this would do what you want, without the permissions headache, and avoiding a trigger.

IF EXISTS(select * from [SSISDB].[internal].[projects] WHERE created_time >= DATEADD(hh,-1,DATEADD(hh, DATEDIFF(hh,0,getdate()), 0)) --The last hour + a bit, ie 4:16pm rounds douwn to 3pm. ) BEGIN EXEC msdb.dbo.sp_start_job @job_name = 'SSISDB Catalog Synch' END

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

lmarkum SSCertifiable Points: 7985 More actions · Answer 5

And this is why I like forums and reading what other people are doing! Thanks so much Lowell. Much simpler solution than the road I was on.