Accesing owner table problem

[isonlyme]

HI,

I have a Database Called PRODDB the owner is USER1 and a table called MYTABLE that was created by USER1 and also USER1 is owner of the table.

I have setup the exact same database and user in TEST enviorment.

In test a DTS that reads a file and upload it in a table and do a simple select "select * from MYTABLE" this works.
Then I copy "MYTABLE" and data using a simple dts to prod and then copy the DTS to prod also.

My problem:
In prod when i execute the dts it gave me an error :


Step Error Source: Microsoft OLE DB Provider for SQL Server
Step Error Description:Invalid object name 'MYTABLE'.
Step Error code: 80040E37
Step Error Help File:
Step Error Help Context ID:0

i realize i have to add the owner on the select:
"Select * USER1.MYTABLE"

but in test i only have:
"Select * from MYTABLE

Why it doesnt work on prod? I created a role and gave all permissions "select insert update delete dri" but still wont work.

Any ideas will be appriciated sine there are more then 20 DTS and hopefully i dont have to add the OWNER to all the statements.

Thanks...in advanced

 

[mrdenny]

For this to work, the jobs that run the dts packages will need to be owned by user1. If they aren't you'll need to update the packages.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Catadmin]

This issue is more likely the difference between your permissions on Dev and your permissions on Production. Or the permissions difference on the account that is running the DTS package.

Look up "Owner of Database object" in Books Online.

For example, user John is a member of the db_owner fixed database role, but not the sysadmin fixed server role, and creates table T1. All users, except John, who want to access T1 must qualify T1 with the user name John. If T1 is not qualified with the user name John, SQL Server first looks for a table named T1 owned by the current user and then owned by dbo. If the current user and dbo do not own a table named T1, an error is returned. If the current user or dbo owns another table named T1, the other table named T1, rather than John.T1, is used.
[/code]

Regardless, it is always safer to identify the owner.table than to make an assumption and have it fail due to permissions.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[isonlyme]

thanks for yuor inputs,

i'll check on this and let you know the results. So far the user on both enviorments have the same security level...

let you know more during the day.

 

[isonlyme]

well after all your suggestions everything worked. the owner of the job on some cases was another user.

thanks for the help!!