|
|
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.
Navigation:
[Reply to this message]
|