SQL Server Performance

Non-sysadmin unable to migrate DTS Packages to SSIS

Discussion in 'SQL Server 2005 Integration Services' started by DBADave, Aug 2, 2007.

  1. DBADave New Member

    Yet another call with Microsoft was placed yesterday, this time to find out why a non-sysadmin appears to need db_datareader and db_datawriter in MSDB in order to migrate DTS packages to SSIS. No such permissions were required in 2000 to migrate a package from one server to another.
    Here is what we have tried.
    • From SQL Server Management Studio. Connect to an instance of the SQL Server 2005 Database Engine, right-click the Data Transformation Services node in Object Explorer under ManagementLegacy, and select Migration Wizard.
    These instructions are found in the following link.
    The problem occurs when a non-sysadmin, with no MSDB permisssions except db_dtsadmin, tries to migrate his own DTS package. Trying to expand Legacy produces the error "Failed to retrieve data for this request...Select permission denied on object 'sysdtspackages', database 'msdb', schema 'dbo'.(Microsoft SQL Server, Error:229)
    The user has a login id on the SQL 2005 server with access only to the user database pertaining to his DTS package code. His id is a SQL Server login id, not a Windows account. The difference with SQL Server login ids and Windows accounts, as they pertain to SSIS packages, is with the instructions followed to eliminate the Access Denied error when accessing a remote server. See http://msdn2.microsoft.com/en-us/library/aa337083.aspx. These instructions can only be followed for Windows accounts and not SQL Server accounts. I'm not sure if this pertains to the DTS migration error, but I do need to bring that to Microsoft's attention.
    Any suggestions? Am I doing something wrong or is this a bug?
  2. satya Moderator

    Select permission denied on object 'sysdtspackages', database 'msdb', schema 'dbo'
    Have you checked for that login whether the permission exists on sysdtspackage?
  3. DBADave New Member

    No. I shouldn't need to grant access to a system view or table. In SQL 2000 this was not necessary when moving DTS packages from one instance to another. Besides, Microsoft should have granted the appropriate access to the role db_dtsadmin.
    Thanks, Dave
  4. satya Moderator

    Do they have public access on MSDB?
    Extract from BOL
    The fixed database-level roles work in conjunction with user-defined roles. The user-defined roles are the roles that you create in SQL Server Management Studio and then use to assign permissions to packages. To access a package, a user must be a member of the user-defined role and the pertinent Integration Services fixed database-level role. For example, if users are members of the AuditUsers user-defined role that is assigned to a package, they must also be members of db_dtsadmin, db_dtsltduser, or db_dtsoperator role to have read access to the package.
    If you do not assign user-defined roles to packages, access to packages is determined by the fixed database-level roles.
    If you want to use user-defined roles, you must add them to the msdb database before you can assign them to packages. You can create new database roles in SQL Server Management Studio.
  5. DBADave New Member

    Hi Satya,
    Yes. The user id is a member of the public role in MSDB. The Microsoft technician we are dealing with insists they need db_datareader and db_datawriter access in MSDB, which from our perspective would be a security hole. This was not necessary in SQL 2000. This morning we will be asking Microsoft to escalate the issue to a senior level technician.
  6. satya Moderator

    Absolutely agree with you, if I were you will not accept that for MSDB!
    appreciate your feedback though, will try to reproduce at my end to see.
  7. DBADave New Member

    We spoke with a different technician today and he also says that either db_datareader and db_datawriter need to be assigned in MSDB to the account trying to import or migrate the package or we need to grant SELECT, INSERT and DELETE against sysdtspackages. He said this is documented internally and not in BOL. I insisted it is a bug since in the past Microsoft has recommended we not grant permissions against system tables and this problem does not exist in SQL 2000. There are at least two issues.
    1. When expanding the tree under Management - Legacy - Data Transformation Services we receive the initial "Select permission was denied on object 'sysdtspackages'. If you click 'OK' and right click Data Transformation Services you can successfully Migrate a DTS package to SSIS. This is because package migration does not involve sysdtspackages but instead sysdtspackages90.
    2. If you choose to Import a package instead of Migrate, you will receive 'The Select permission was denied ...' and 'The Delete permission was denied...', both on sysdtspackages. I believe this is caused because an Import is taking an existing DTS package and copying it to 2005 to be used in backward compatibility mode. An Import involves accessing sysdtspackages. I'm guessing the DELETE permission is needed to delete any existing record in sysdtspackages for situations where you previously imported the package and now wish to import an updated version. In our case no previous package existed so there should have been no need for SQL Server to attempt a DELETE.
    I pointed out to Microsoft that if someone wanted to Migrate a package, they still receive the error in item #1 above, which means their code is accessing sysdtspackages prior to any choice to Import (sysdtspackages) or Migrate (sysdtspackages90) a package. I insisted this behavior is a bug and was told they would investigate it further.
    We also told Microsoft that granting db_datareader and db_datawriter to MSDB is a security risk that should not be an option. In environments subject to Sarbanes-Oxley compliance their solution could be an audit issue.
    This is the second issue in a row where Microsoft said they have some internal non-public documentation regarding a problem. I told them this should be public knowledge so people do not waste time troubleshooting the problem.

Share This Page