security issue when running a T-SQL task

Question

Post reply

security issue when running a T-SQL task

DamianC

SSCrazy Eights

Points: 8006
More actions
November 16, 2018 at 5:20 am

#380875

Hello

I am having security issues when attempting to create a dataflow import against a specific table in the msdb DB

I can execute this T-SQL without issue from SSMS : select name from dbo.sysjobs

Yet, if I perform the following in SSIS, I get 'SELECT permission was denied':

Create a new package within a project
Add a Data Flow Task
Add a connection to msdb on the required server (Tests ok)
Select Data access mode - SQL command
Enter select name from dbo.sysjobs in as SQL comand text
Select Preview

How can I create the task as though it's me running/previewing it?

Thanks

- Damian

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

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

Phil Parkin SSC Guru Points: 245813 More actions · Answer 1

Yet, if I perform the following in SSIS, ...:

What do you mean by this, exactly?

From SSDT?
Right-click / Execute from SSISDB?
As part of a SQL Agent job?
From a T-SQL stored procedure?
From a .Net application?
something else?

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

DamianC SSCrazy Eights Points: 8006 More actions · Answer 2

DamianC

SSCrazy Eights

Points: 8006

November 16, 2018 at 6:09 am

#2013782

Hello Phil

In this instance SQL 2008 R2 BIDS

Thanks

- Damian

Phil Parkin SSC Guru Points: 245813 More actions · Answer 3

DamianC - Friday, November 16, 2018 6:09 AM

Hello Phil

In this instance SQL 2008 R2 BIDS

Thanks

In that case, I have no idea what the problem is ... it should run as you from within BIDS, assuming your MSDB connection uses Windows Authentication.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

DamianC SSCrazy Eights Points: 8006 More actions · Answer 4

Phil Parkin - Friday, November 16, 2018 6:19 AM

In that case, I have no idea what the problem is ... it should run as you from within BIDS, assuming your MSDB connection uses Windows Authentication.

Yes that's what I thought and it uses windows authentication
I'll do a little more digging into the security side

Thanks

- Damian

DamianC SSCrazy Eights Points: 8006 More actions · Answer 5

DamianC

SSCrazy Eights

Points: 8006

November 16, 2018 at 6:58 am

#2013792

ok, very confusing
Looks like select permission on msdb had been revoked earlier today

Confusing, as up until about 30 minutes ago I was able to execute the T-SQL select statement via SSMS with no issues
Now getting permission issues there

Looks like it should be an easy fix

- Damian

Phil Parkin SSC Guru Points: 245813 More actions · Answer 6

DamianC - Friday, November 16, 2018 6:58 AM

ok, very confusing
Looks like select permission on msdb had been revoked earlier today

Confusing, as up until about 30 minutes ago I was able to execute the T-SQL select statement via SSMS with no issues
Now getting permission issues there

Looks like it should be an easy fix

At least there's a sensible explanation ...!

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.