DTS Package Owner Incorrect - Why?

Question

DTS Package Owner Incorrect - Why?

DBADave

SSChampion

Points: 10962
More actions
August 30, 2007 at 1:17 pm

#176366
We performed the following steps.
1. Create a SQL Server login id named DTSUser with no database permissions.
2. Connect to an instance of SQL Server using Enterprise Manager, with the connection properties of DTSUser
3. Open an existing DTS package and "SAVE AS" or Create a new DTS package and "SAVE"
4. The DTS package owner is always listed as the Windows Account connected to the PC or server, where the package was created
Why? Is this a bug?
SP_WHO shows DTSUser being connected, but DTS somehow believes the domain account should be the owner of the package.
We are running SQL 2000 SP3a.
Thanks, Dave

Viewing 7 posts - 1 through 6 (of 6 total)

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

EdVassie SSC Guru Points: 60472 More actions · Answer 1

I think the behaviour is by design. You can change the package owner if you need to. However, the package creator is stored within the DTS object and cannot be changed, but this does not cause any issues.

If you do think it is a bug the chances of MS fixing it are about zero, as support for SQL 2000 is stabilised and they will now only fix the most serious secirity issues.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

JeremyE SSCoach Points: 16201 More actions · Answer 2

If you really want to change the owner you can modify it in msdb.sysdtspackages.

DBADave SSChampion Points: 10962 More actions · Answer 3

I'm not concerned with changing the owner. That part is simple. I'm trying to determine why DTS is ignoring the login credentials established within Enterprise Manager and instead using the Windows account connected to the network. sp_who2 reports the correct login information, which is the SQL Server login id used to connect to EM.

Dave

JeremyE SSCoach Points: 16201 More actions · Answer 4

This may be a designed behavior by SQL Server in order to identify which user created the DTS package. I have confirmed this happens in SP4 also.

DBADave SSChampion Points: 10962 More actions · Answer 5

That stinks. We were trying to come up with a process for developers and consultants to maintain DTS packages. The developers have SA on our development servers so their permissions are not a problem. At some point we may restrict their access to avoiding using SA, but that's another story. Consultants are not given SA rights so we have to change the package owners for the consultants. The hope was to create a SQL Server login id that owns all packages. Now it looks like we have to create an Activie Directory account and ask all developers and consultants to use "Run As" and create all packages using this account.

Thanks, Dave

Greg Charles SSC-Forever Points: 45403 More actions · Answer 6

SQL Server has used only Windows logins for DTS package ownership for as long as I've used DTS, since SQL 7.0. I've never been curious enough to find out why, but I suspect that it's to facilitate importing and exporting to and from locations outside of the current SQL Server instance. For example, a SQL Server login wouldn't be able to read or write to a text file residing in the file system.

Greg