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

• Phil Parkin

  SSC Guru

  Points: 247155

  More actions

  November 16, 2018 at 5:29 am

  #2013773

  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

  November 16, 2018 at 6:09 am

  #2013782

  Hello Phil

  In this instance SQL 2008 R2 BIDS

  Thanks

  - Damian

• Phil Parkin

  SSC Guru

  Points: 247155

  More actions

  November 16, 2018 at 6:19 am

  #2013787

  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

  November 16, 2018 at 6:29 am

  #2013789

  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

  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: 247155

  More actions

  November 16, 2018 at 7:09 am

  #2013793

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

  ---