Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
DBADave
Constraint Violating Yak Guru
366 Posts |
 Posted - 2007-08-02 : 13:59:58
|
| 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 Management\Legacy, and select Migration Wizard. These instructions are found in the following link.http://msdn2.microsoft.com/en-us/library/ms143496.aspxThe 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?Dave |
|
|
|
|
|
|
|
Non-sysadmin unable to migrate DTS Packages