June 27, 2005 at 1:23 pm
I am using SQL Server 2000 (SP3). I have a user that is a member of the "public" and "db_owner" database roles. This user is not a member of any server roles. When he tries to save a DTS Package in which I am the owner he receives the following error:
"Only the owner of the DTS Package (Package Name) or a member of "sysadmin" role may create new versions of it."
Is there any way to get around this error without making him a member of the "sysadmin" fixed server role, which would allow him to perform any activity in SQL Server.
Does the database roles apply to DTS Packages?
Thanks in advance, Kevin
June 27, 2005 at 9:35 pm
one way is save your packages with passwords, user enter the password then can do edit/save/exec.
Another way is grant user with execute on sp_add_dtspackage, sp_get_dtspackage, sp_enum_dtspackages. Can be very harmful if user is 'not trust worthy'.
June 28, 2005 at 5:58 am
the user being a member of database owner role gives him permission to create and save DTS packages. the problem you are having is that he can not update DTS packages save where he is not the owner. you can make the user the owner of the DTS package by
update msdb..sysdtspackages
set owner_sid = (select sid from master..sysxlogins
where name = 'user login')
where name = 'name of dts package'
or
do what we do:
every member of an development team uses the same SQL login, thereby giving every member rights to update each other's DTS packages
June 28, 2005 at 6:26 am
You definitely should NOT update system tables directly (see previous post regarding sysdtspackages).
If this is a one-time thing, you can transfer ownership of the package using the following undocumented procedure:
sp_reassign_dtspackageowner [@name =] 'name',[@id =] 'id',[@newloginname =] 'newloginname' |
[@name =] 'name'
The package name.
[@id =] 'id'
This is the uniqueidentifier for the package. A name may not necessarily be unique.
[@newloginname =] 'newloginname'
The new Owner name. SQL Server login example 'sa', NT Integrated example 'Domain\Username'
If this isn't a one-time thing, you'll be better off using a shared login for DTS package maintenance (DTSMaint, or something like that).
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply