SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Invalid Object Name ... Permissions / Owner Issue
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

selliott_sql
Starting Member

USA
2 Posts

Posted - 05/28/2008 :  17:31:44  Show Profile  Reply with Quote
I have a stored procedure owned by [dbo] and inside of this stored procedure I have the following code:


if exists 
(
	select 	1 
	from 	[INFORMATION_SCHEMA].[TABLES] 
	where 	[TABLE_TYPE] = 'BASE TABLE' 
		AND 
		[TABLE_NAME] = 'tmpExample'
) 
drop table [tmpExample]

create table [tmpExample]
(
	intID 		int IDENTITY(1,1) primary key,
	intPrimaryID 	int,
	strFirstName 	varchar(50),
	strLastName 	varchar(50),
	strAreaCode 	varchar(3),
	strPhoneNumber 	varchar(7),
	intSecondaryID 	int	
)


After this code I do various INSERT and DELETE statements against this table. Now ... the problem lies in how its executed. I have another user, say [user1], that executes this stored procedure from a .Net application through the Microsoft's SQLClient object. When the stored procedure is executed [user1] owns the [tmpExample] table after it's created, however it throws an error of Invalid object name 'tmpExample'.

The odd thing is that if you take that code out of the stored procedure, throw it in SQL Query Analyzer, and run it, it works. Another odd thing is that if you execute the stored procedure from SQL Query Analyzer it fails just as in the .Net app. The fix was that I had to create the stored procedure as [user1].

My question is, why if back when [dbo] owned the stored procedure and [user1] ran it (which had [user1] owning the [tmpExample] table), is it throwing that exception of Invalid object name 'tmpExample'? [user1] owns that table so why could it not then modify it by INSERT, UPDATE, and/or DELETE statements? And why does executing the stored procedure from SQL Query Analyzer fail yet the code itself in SQL Query Analyzer works?

Please help my madness!!!!!

Thank you,

Scott in Texas
.Net Developer (ASP, VB, C#)

tkizer
Almighty SQL Goddess

USA
36949 Posts

Posted - 05/28/2008 :  17:33:15  Show Profile  Visit tkizer's Homepage  Reply with Quote
Make sure to specify dbo as the owner at the time of creation so that you avoid this issue.

CREATE TABLE dbo.TableName ...

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx
Go to Top of Page

selliott_sql
Starting Member

USA
2 Posts

Posted - 05/29/2008 :  10:31:08  Show Profile  Reply with Quote
Ok, so that's another way to get it to work.

The question still remains, why since [user1] owns that table how could it not then modify it by INSERT, UPDATE, and/or DELETE without the Invalid Object Error? And why does executing the stored procedure from SQL Query Analyzer fail yet the code itself in SQL Query Analyzer works?


Thank you,

Scott in Texas
.Net Developer (ASP, VB, C#)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000