Post reply

DTS Execution and Security

  • March 21, 2002 at 11:53 am

    #78418

    Hello,

    I have a user login account that has db_owner rights over a single database on a development server. I want to give this user the ability to refresh/overwrite the contents of this development database with a copy of production. They need to do this on-demand.

    To do this, I created a Local DTS package, but when the user tries to execute the package, they get the following error:

    Error source: Microsoft OLE DB Provider for SQL Server

    Error description: Login failed for user '<my user's name>'

    The package uses the "sa" ID and passowrd to connect to the source and target databases and the package runs perfectly when I'm logged on as "sa".

    Any suggestions would be greatly appreciated.

    Thanks,

    Bob

  • March 21, 2002 at 2:15 pm

    #425875

    Might be some type of issue with the user executing packages. Can they execute some other simple package?

    Is this a copy db task?

    Steve Jones

    steve@dkranch.net

  • March 22, 2002 at 5:07 am

    #425903

    Steve - Thanks for the reply. It is a Transfer SQL Server Objects task. It is set to transfer all objects. Under the options, I kept all of the defaults except I have disabled the "Transfer database users and database roles" option.

  • March 22, 2002 at 6:01 am

    #425914

    Steve - to answer your other question - I created an Execute SQL task with a simple SQL select statement and I received the same error when I attempt to execute it.

  • March 22, 2002 at 6:26 am

    #425916

    Steve - I continued investigating and found the following tech note that resolved my problem:

    FIX: SEM 7.0 Login Failed Message When Editing or Running DTS Package (Q247313)

    In a nuthsell, it was related to the connection properties in the SQL Server registration.

    Thanks again for your response,

    Bob

Viewing 5 posts - 1 through 4 (of 4 total)

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