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 6 (of 6 total)

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

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

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

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: 246993 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.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

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

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel