Package Ownership

Ok... Now I'am panicking.... I dont know how to resolve this issue...

Package created by me, imported to sql server by dba, its executed under a different user id (all are NT logins)

Execution Command

Code Snippet

DTEXEC /SQL "\pkg1" /SERVER SERVER1 /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E

Below is the error.

Code Snippet

Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

Started: 9:01:51 AM

Could not load package "\pkg1" because of error 0xC0014062.

Description: The LoadFromSQLServer method has encountered OLE DB error code 0x80040E14 (Only the owner of DTS Package 'pkg1' or a member of the sysadmin role may create new versions of it.). The SQL statement that was issued has failed.

Source:

Started: 9:01:51 AM

Finished: 9:01:51 AM

Elapsed: 0.156 seconds

Please some body help me on this.
Thanks

And what happens when you execute it with your ID?|||

It fails.

Scenarios:-
1. I Develop, I Import, I Run - every thing works fine.

2. I Develop, DBA Imports to SQL SERVER, I Run - Package fails
3. I Develop, DBA Imports to SQL SERVER, Another user runs - Package fails

4. I Develop, DBA Imports to SQL SERVER, DBA Runs - Package runs.

Finally I figuring it out that either imported user has to run or system admin has to run?

Thanks

|||It's the ProtectionLevel property of the package. Search this forum for "ProtectionLevel" for a plethora of threads on this topic. You'll likely want to use EncryptSensitiveWithPassword or DontSaveSensitive.|||

Phil Brammer wrote:

It's the ProtectionLevel property of the package. Search this forum for "ProtectionLevel" for a plethora of threads on this topic. You'll likely want to use EncryptSensitiveWithPassword or DontSaveSensitive.

One other thing... Are you importing to MSDB or the file system? I ask because if you are importing into MSDB, one other thing to try is to set the security upon import to "Rely on SQL Server Roles and Storage" or something like that.|||

Phil Brammer wrote:

It's the ProtectionLevel property of the package. Search this forum for "ProtectionLevel" for a plethora of threads on this topic. You'll likely want to use EncryptSensitiveWithPassword or DontSaveSensitive

One other thing... Are you importing to MSDB or the file system? I ask because if you are importing into MSDB, one other thing to try is to set the security upon import to "Rely on SQL Server Roles and Storage" or something like that.

We are importing to MSDB. But even file system was giving the same problem.

I already set this property to dontsavesensitive in the designer.

This property is also available when we import the package to sql server so far we left it blank, i'm going to try to set this as dontsavesensitive in this.

Thanks

|||This link provided very useful information to me: http://support.microsoft.com/kb/918760/

|||

Got this resolved by giving the executing user the role of db_dtsoperator. Now the package is fialing with different error as below.

Code Snippet

Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available

This error is coming even when I have set the protection level as Dont Save Sensitive in the dtsx file (in VS 2005). Should I also set this when I import to MSDB?

Thanks

sql

Owership of database objects, Yu'll login to use?

I am very new to SQL Server 2000 but was a Sybase DBA for 2 years. In
Sybase we would alias a developer to DBO (of that database only) so
that all objects (tables/stored procedures) would show up as owned by
DBO.
In SQL Server it looks to me that the only way to get obects owned by
DBO is to load them as SA or add the developer to the fixed role of
sysadmin. Neither of those appears to be the way to go.
So I'm wondering how other shows handle this issue. Do you set up an
specific account for loading objects and maintaining a SQL server
database?
Thanks,
Randy K
wawork@.hotmail.comHi Randy
I worked with Sybase for 8 years, and have been working with Microsoft SQL
Server since its inception.
In SQL Server, you can alias logins to the dbo user of a database exactly
the same way you do with Sybase, with the sp_addalias procedure.
Is it possible you're trying to achieve this functionality using the
db_owner role? Putting a user in that role will give her all the rights and
privileges of the database owner, but her name will still be her own name,
and not dbo. So any objects she creates, by default will not be owned by
dbo. However, someone in the db_owner role can create objects and specify
that the owner should be dbo:
CREATE TABLE dbo.newtable
(column ...)
So there are really two different things going on. You can actually give
someone the name dbo using sp_addalias, or you can put them in the db_owner
role, and they can specify that new obects are to be owned by dbo.
It's actually recommended that you always specify the owner name of objects,
both when creating them and when referencing them, and if you get into this
habit, you don't lose anything by just using the db_owner role.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Randy K" <wawork@.hotmail.com> wrote in message
news:3f27f6bc.59865578@.msnews.microsoft.com...
> I am very new to SQL Server 2000 but was a Sybase DBA for 2 years. In
> Sybase we would alias a developer to DBO (of that database only) so
> that all objects (tables/stored procedures) would show up as owned by
> DBO.
> In SQL Server it looks to me that the only way to get obects owned by
> DBO is to load them as SA or add the developer to the fixed role of
> sysadmin. Neither of those appears to be the way to go.
> So I'm wondering how other shows handle this issue. Do you set up an
> specific account for loading objects and maintaining a SQL server
> database?
> Thanks,
> Randy K
> wawork@.hotmail.com|||Unfortunately, a user with the db_owner role can
> not change the ownership of a object when creating it using the ESRI
> software.
Yes, this can be a problem. Even Microsoft's own Enterprise Manager didn't
allow specifying a different owner in SQL Server 7, and although SQL 2000
allows it, it is not obvious how to do it. That's one of the reasons why I
never use GUIs to create tables. :-)
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Randy K" <wawork@.hotmail.com> wrote in message
news:3f284734.18529640@.msnews.microsoft.com...
> Thank you Kalen that's what I was looking for. I had always used
> Sybase Central GUI to alias users as DBO and mistaking thought
> assigning a user to the db_owner role in SQL Server was the same
> thing.
> I'll need to do some testing since we are using SQL Server with ArcSDE
> by ESRI on top. Of the two methods only the first, spp_addalias,
> works with ArcSDE. Unfortunately, a user with the db_owner role can
> not change the ownership of a object when creating it using the ESRI
> software.
>
> On Wed, 30 Jul 2003 10:21:16 -0700, "Kalen Delaney"
> <replies@.public_newsgroups.com> wrote:
> >Hi Randy
> >
> >I worked with Sybase for 8 years, and have been working with Microsoft
SQL
> >Server since its inception.
> >
> >In SQL Server, you can alias logins to the dbo user of a database exactly
> >the same way you do with Sybase, with the sp_addalias procedure.
> >
> >Is it possible you're trying to achieve this functionality using the
> >db_owner role? Putting a user in that role will give her all the rights
and
> >privileges of the database owner, but her name will still be her own
name,
> >and not dbo. So any objects she creates, by default will not be owned by
> >dbo. However, someone in the db_owner role can create objects and specify
> >that the owner should be dbo:
> >
> >CREATE TABLE dbo.newtable
> >(column ...)
> >
> >So there are really two different things going on. You can actually give
> >someone the name dbo using sp_addalias, or you can put them in the
db_owner
> >role, and they can specify that new obects are to be owned by dbo.
> >
> >It's actually recommended that you always specify the owner name of
objects,
> >both when creating them and when referencing them, and if you get into
this
> >habit, you don't lose anything by just using the db_owner role.
> >
> >--
> >HTH
> >--
> >Kalen Delaney
> >SQL Server MVP
> >www.SolidQualityLearning.com
> >
> >
> >"Randy K" <wawork@.hotmail.com> wrote in message
> >news:3f27f6bc.59865578@.msnews.microsoft.com...
> >> I am very new to SQL Server 2000 but was a Sybase DBA for 2 years. In
> >> Sybase we would alias a developer to DBO (of that database only) so
> >> that all objects (tables/stored procedures) would show up as owned by
> >> DBO.
> >>
> >> In SQL Server it looks to me that the only way to get obects owned by
> >> DBO is to load them as SA or add the developer to the fixed role of
> >> sysadmin. Neither of those appears to be the way to go.
> >>
> >> So I'm wondering how other shows handle this issue. Do you set up an
> >> specific account for loading objects and maintaining a SQL server
> >> database?
> >>
> >> Thanks,
> >> Randy K
> >> wawork@.hotmail.com
> >
> >
>