Posted by Radu on 09/27/05 20:13

Hi. Thru a sproc, I drop & re-create some temp tables.

When I call that sproc from the client, though, I cannot drop the
tables.
I need to allow the user, say "Alex", to drop/create tables (actually,
that would be DDL). Which role should "Alex" assume ? How do I do that
?

I run the following sproc named, say, "CREATE_TABLE" (SNIP):
____________________________________________________________________
Set @StrSQL = 'if exists (select * from dbo.sysobjects where id =
object_id(N''[dbo].[' + @TableName + ']'') and OBJECTPROPERTY(id,
N''IsUserTable'') = 1) drop table [dbo].[' + @TableName + ']'

Exec (@StrSQL)

Set @StrSQL = 'CREATE TABLE [dbo].[' + @TableName + '] (

[GROUP] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
...........
[Stuff] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

) ON [PRIMARY]'
Exec (@StrSQL)

Set @StrSQL = 'GRANT SELECT , UPDATE , INSERT , DELETE ON [dbo].['
+ @TableName + '] TO [Alex]'

Exec (@StrSQL)
____________________________________________________________________

As you can see, it is dynamic, because I need to repeat it for many
@TableName values - that means, further more, that I will be executing
this in the context of the current user, Alex, and therefore I have to
give Alex rights to both executing the sproc and to the tables referred
to by the sproc, as specified by @TableName.

I created a _TEST sproc which contains only the following:
_______________________________________________
CREATE PROCEDURE _TEST AS

DROP TABLE [dbo].[SomeTable]

RETURN
_______________________________________________

When I execute it from the client, thru ADODB, on user Alex, I get
"User does not have permission to execute this operation on table
SomeTable"

The table has been created thru "CREATE_TABLE", above

Please help, I have to finish this tomorrow, and I'm under tons of
pressure.

Thanks a lot,
Alex.

[Back to original message]