Post reply

Other Users Cannot Execute SSIS Packages

  • August 25, 2008 at 3:01 am

    #123193

    Hi,

    I recently migrated some DTS packages from SQL 2000 to SQL 2005. I have successfully run the wizard to migrate the packages to SSIS but now only I can run the packages successfully.

    I have changed the connections to use SA instead of integrated authentication but that did not work.

    The user I am in contact with has the same admin rights on the server and the same DB access in SQL.

    PLease could someone help.

    Regards,

    Brad

  • August 25, 2008 at 3:54 am

    #862434

    are these packages stored in the MSDB database?

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • August 25, 2008 at 4:55 am

    #862450

    Yes they are.

  • August 25, 2008 at 9:39 am

    #862598

    ensure users are mapped to one of the MSDB standard DTS roles

    db_dtsadmin, db_dtsltduser, and db_dtsoperator

    or create and assign a custom role. Users need to have correct permissions to execute the packages

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • August 25, 2008 at 10:08 am

    #862621

    Hi Brad.

    I ran into a similar situation, about 6 months ago.

    Try this - not sure if it will work, but it's worth the 5 minutes to try.

    Open the package, and go to Properties - of the package not of any Control Flow or Data Flow step.

    There is a section, "Security", and a property, "ProtectionLevel".

    It defaults to "EncryptSensitiveWithUserKey".

    Change that to "EncryptSensitiveWithPassword" or something...give the PackagePassword property a simple password like "password".

    Maybe even a blank password will work.

    Again, this proved to be a workable solution to me earlier this year.

  • August 25, 2008 at 1:12 pm

    #862744

    can you tell what error message do u see there??

  • August 25, 2008 at 11:34 pm

    #862903

    HI Perry,

    Thanks a lot. WHere do I find these roles?

    Regards,

    Brad

  • August 25, 2008 at 11:35 pm

    #862904

    Thanks Jason.

    Does that mean the package will need a password to be executed?

    Regards,

    Brad

  • August 25, 2008 at 11:37 pm

    #862905

    The package runs - so not an initial error. But during the validation there are a whole lot of connection errors.

  • August 26, 2008 at 1:19 am

    #862943

    Hi Guys,

    I fixed it. It was something to do with security. I changed the user's profile a little and it seems to be working.

    Thanks for the help.

    Brad

  • August 26, 2008 at 10:07 am

    #863248

    Brad (8/25/2008)

    HI Perry,

    Thanks a lot. WHere do I find these roles?

    Regards,

    Brad

    the roles are in the MSDB for that instance. Edit a sql users mapping and under MSDB you'll see the roles. Check BOL for more info

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

Viewing 11 posts - 1 through 10 (of 10 total)

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