Grant Create/Drop user Table permissions?

HI All,
I have a stored proc that runs and creates a temporary table for collecting
data and then drops it when it is done. My problem is that I can, as DBO,
run this fine but my users can not. How do I allow Create/Drop User tables
from a stored proc? Let me phrase that a different way; what kind of
permissions do I need to set up so that user's can run this stored proc that
creates/drops a temporary table? The stored proc already as the required
EXEC permissions for the user/groups to run it.
Thanks very much,
John.Hi John,
Your users don't need any special permissions to create temporary tables,
that is if you use real temporary tables, the ones prefixed with a #
character. From your narrative I get the impression that you use permanent
tables as temporary tables, but that is not advisable.
--
Jacco Schalkwijk
SQL Server MVP
"John Rugo" <jrugo@.patmedia.net> wrote in message
news:eytDgEa6DHA.2412@.TK2MSFTNGP09.phx.gbl...
> HI All,
> I have a stored proc that runs and creates a temporary table for
collecting
> data and then drops it when it is done. My problem is that I can, as DBO,
> run this fine but my users can not. How do I allow Create/Drop User
tables
> from a stored proc? Let me phrase that a different way; what kind of
> permissions do I need to set up so that user's can run this stored proc
that
> creates/drops a temporary table? The stored proc already as the required
> EXEC permissions for the user/groups to run it.
> Thanks very much,
> John.
>|||Hi,
You should give the below prev. to the normal database user.
grant create table to username
Drop table is not necessory because the owner who create the table can drop
the table.
Thanks
Hari
MCDBA
"John Rugo" <jrugo@.patmedia.net> wrote in message
news:eytDgEa6DHA.2412@.TK2MSFTNGP09.phx.gbl...
> HI All,
> I have a stored proc that runs and creates a temporary table for
collecting
> data and then drops it when it is done. My problem is that I can, as DBO,
> run this fine but my users can not. How do I allow Create/Drop User
tables
> from a stored proc? Let me phrase that a different way; what kind of
> permissions do I need to set up so that user's can run this stored proc
that
> creates/drops a temporary table? The stored proc already as the required
> EXEC permissions for the user/groups to run it.
> Thanks very much,
> John.
>|||Excellent Idea! I completely forgot about that.
Thanks very much :)
By the way, is it necessary to drop temporary tables and or check for their
existence before creating them?
Thanks again ,
John.
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:OUKZyOa6DHA.3704@.tk2msftngp13.phx.gbl...
Hi John,
Your users don't need any special permissions to create temporary tables,
that is if you use real temporary tables, the ones prefixed with a #
character. From your narrative I get the impression that you use permanent
tables as temporary tables, but that is not advisable.
--
Jacco Schalkwijk
SQL Server MVP
"John Rugo" <jrugo@.patmedia.net> wrote in message
news:eytDgEa6DHA.2412@.TK2MSFTNGP09.phx.gbl...
> HI All,
> I have a stored proc that runs and creates a temporary table for
collecting
> data and then drops it when it is done. My problem is that I can, as DBO,
> run this fine but my users can not. How do I allow Create/Drop User
tables
> from a stored proc? Let me phrase that a different way; what kind of
> permissions do I need to set up so that user's can run this stored proc
that
> creates/drops a temporary table? The stored proc already as the required
> EXEC permissions for the user/groups to run it.
> Thanks very much,
> John.
>|||Thanks for the correct syntax; I was drawing a mind blank this morning. Is
it possible to grant the same prev to a group instead of individual users?
John.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:us9TIRa6DHA.3360@.tk2msftngp13.phx.gbl...
Hi,
You should give the below prev. to the normal database user.
grant create table to username
Drop table is not necessory because the owner who create the table can drop
the table.
Thanks
Hari
MCDBA
"John Rugo" <jrugo@.patmedia.net> wrote in message
news:eytDgEa6DHA.2412@.TK2MSFTNGP09.phx.gbl...
> HI All,
> I have a stored proc that runs and creates a temporary table for
collecting
> data and then drops it when it is done. My problem is that I can, as DBO,
> run this fine but my users can not. How do I allow Create/Drop User
tables
> from a stored proc? Let me phrase that a different way; what kind of
> permissions do I need to set up so that user's can run this stored proc
that
> creates/drops a temporary table? The stored proc already as the required
> EXEC permissions for the user/groups to run it.
> Thanks very much,
> John.
>|||Hi John,
Temporary tables are dropped automatically when they go out of scope, which
means that if you create a temporary table in a stored procedure the
temporary table will be dropped when the stored procedure completes. In any
case, temporary tables are dropped when the user diconnects from the
database, and internally temporary tables created by different users have
different names, although they all seem to have the same name to the users,
so multiple users can create the same temporary table at the same time.
--
Jacco Schalkwijk
SQL Server MVP
"John Rugo" <jrugo@.patmedia.net> wrote in message
news:uOWIdea6DHA.2760@.TK2MSFTNGP09.phx.gbl...
> Excellent Idea! I completely forgot about that.
> Thanks very much :)
> By the way, is it necessary to drop temporary tables and or check for
their
> existence before creating them?
> Thanks again ,
> John.
> "Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
> news:OUKZyOa6DHA.3704@.tk2msftngp13.phx.gbl...
> Hi John,
> Your users don't need any special permissions to create temporary tables,
> that is if you use real temporary tables, the ones prefixed with a #
> character. From your narrative I get the impression that you use permanent
> tables as temporary tables, but that is not advisable.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "John Rugo" <jrugo@.patmedia.net> wrote in message
> news:eytDgEa6DHA.2412@.TK2MSFTNGP09.phx.gbl...
> > HI All,
> >
> > I have a stored proc that runs and creates a temporary table for
> collecting
> > data and then drops it when it is done. My problem is that I can, as
DBO,
> > run this fine but my users can not. How do I allow Create/Drop User
> tables
> > from a stored proc? Let me phrase that a different way; what kind of
> > permissions do I need to set up so that user's can run this stored proc
> that
> > creates/drops a temporary table? The stored proc already as the required
> > EXEC permissions for the user/groups to run it.
> >
> > Thanks very much,
> > John.
> >
> >
>
>