Invalid Object Name ... Permissions / Owner Issue

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Invalid Object Name ... Permissions / Owner Issue

Author

Topic

selliott_sql
Starting Member

2 Posts

Posted - 2008-05-28 : 17:31:44

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

38200 Posts

Posted - 2008-05-28 : 17:33:15

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

selliott_sql
Starting Member

2 Posts

Posted - 2008-05-29 : 10:31:08

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#)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources