security issue when running a T-SQL task

Question

Post reply

security issue when running a T-SQL task

DamianC

SSCrazy Eights

Points: 8023
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 7 (of 7 total)

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

Phil Parkin SSC Guru Points: 247180 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?

DamianC SSCrazy Eights Points: 8023 More actions · Answer 2

Hello Phil

In this instance SQL 2008 R2 BIDS

Thanks

- Damian

Phil Parkin SSC Guru Points: 247180 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.

DamianC SSCrazy Eights Points: 8023 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: 8023 More actions · Answer 5

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: 247180 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 ...!