Create Table In Store Procedure and inserting data with the Kalen user.

Hello.
First: Sorry for my inglish.
I'need create this "simple" SP
I Work with (SQL 2000 + SP4)
Use TempDB
GO
Create procedure DBO.ProcTest
as
drop table Tempdb..TabTest
create table Tempdb..TabTest (a int)
insert into Tempdb..TabTest values (1000)
GO
grant exec on DBO.ProcTest to public
When I execute this SP whit the user Kalen (She have "Create table"
permission on TempDB) I receive this message
--Invalid object name 'Tempdb..TabTest'
This is beacuse SQL find a Temp.DBO.TabTest or not?. but if execute the
code in the Query analyzer, not have this problem.
The question is: Exist a solution without SQL Dynamic, with persistent table
and creating only one Procedure (not a procedure for each user)?
Thank You
Fernando Flamenco
Ing Sistemas de Informacin
Buenos Aires - Argentina.First of all don't create tables in TempDB. TempDB is a system database.
"Fernando Flamenco" <flamencof@.yahoo.com> wrote in message
news:uD8UmEFgGHA.1260@.TK2MSFTNGP05.phx.gbl...
> Hello.
> First: Sorry for my inglish.
> I'need create this "simple" SP
> I Work with (SQL 2000 + SP4)
> Use TempDB
> GO
> Create procedure DBO.ProcTest
> as
> drop table Tempdb..TabTest
> create table Tempdb..TabTest (a int)
> insert into Tempdb..TabTest values (1000)
> GO
> grant exec on DBO.ProcTest to public
> When I execute this SP whit the user Kalen (She have "Create table"
> permission on TempDB) I receive this message
> --Invalid object name 'Tempdb..TabTest'
> This is beacuse SQL find a Temp.DBO.TabTest or not?. but if execute the
> code in the Query analyzer, not have this problem.
> The question is: Exist a solution without SQL Dynamic, with persistent
> table and creating only one Procedure (not a procedure for each user)?
> Thank You
> Fernando Flamenco
> Ing Sistemas de Informacin
> Buenos Aires - Argentina.
>

Create Table In Store Procedure and inserting data with the Kalen user.

As Mike said, don't create objects in tempdb. However, your stored
procedure is failing because the first thing you are doing is dropping
a table that doesn't exist.
What are you trying to accomplish?
StuThank stu and mike,
I' know: the tempdb is a system database but i use this database like
workspace.
I explain this again, for this
1) Create a user Kalen with the create table permission on tempdb database
2) Connect with this user and execute
if exists (select * from dbo.sysobjects where id =
object_id(N'[TabTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [TabTest]
create table Tempdb..TabTest (a int)
insert into Tempdb..TabTest values (1000)
This code is simple and the more important work
3) Connect with a user sa and create this SP
Create procedure DBO.ProcTest
as
if exists (select * from dbo.sysobjects where id =
object_id(N'[TabTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table Tempdb..TabTest
create table Tempdb..TabTest (a int)
insert into Tempdb..TabTest values (1000)
GO
grant exec on DBO.ProcTest to Kalen
4) Connect with the Kalen user this not work
I receive this message
--Invalid object name 'Tempdb..TabTest'
The question is:
I need create this one SP and not use SQL Dynamic.
Easy'
"Stu" <stuart.ainsworth@.gmail.com> wrote in message
news:1148603889.848594.234520@.j73g2000cwa.googlegroups.com...
> As Mike said, don't create objects in tempdb. However, your stored
> procedure is failing because the first thing you are doing is dropping
> a table that doesn't exist.
> What are you trying to accomplish?
> Stu
>|||"Fernando Flamenco" <flamencof@.yahoo.com> wrote in message
news:%23s7zNOMgGHA.4304@.TK2MSFTNGP05.phx.gbl...
> Thank stu and mike,
> I' know: the tempdb is a system database but i use this database like
> workspace.
<snip> SQL Server uses it as a workspace as well. You're asking for trouble
here. Have fun.</snip>

> I need create this one SP and not use SQL Dynamic.
1) I HIGHLY recommend against forcing your own DDL inside TempDB. At best
you're creating contention within SQL Server for TempDB resources. It's a
System Database for a reason.
2) I don't see the point of what you're doing, but if you're building the
SP in a separate Database from the one you're executing it in (assuming
that's the reason you feel the need to prefix the table with Tempdb..
everywhere), then you need to look at the effect of not using that prefix in
your IF EXISTS statement. I added the database name prefix to your select *
from dbo.sysobjects and object_id statements and dropped the OBJECTPROPERTY
function in favor of checking the XTYPE column. Works great.

> Easy'
Too Easy.sql