Best way to grant create table permissions in sp

[timscronin]

I have inherited a app that uses a stored proc which creates a temp table a select statement and then drops the table when done. I need the users to be granted the ability to create tables at the beginning of the stored proc, and drop this at the end of the sp. What would be the best approach to do this (no I can't go back to the developer and have them change) ,so I need to find a best solution

 

[dafdba]

Just give the user permissions to execute the stored procedure. It is not necessary to give separate permissions to create and drop temp tables within that stored procedure.

 

[timscronin]

there are not temp tables

 

[SQLSister]

Your developers aren't creating a real table and then dropping it are they? Temp tables have names that start with #(Local temp tables) or ##(Global temp tables). If they are using real tables for temporary purposes, then they MUST change their code or you will have lots of problems. This is a very poor programming practice. Users should never, in my opinion, be given rights even temporarily to change tables structures or add or delete tables.

 

[JohnDTampaBay]

Can they be made into temp tables - even global temp tables - to solve this issue? That is the simplest and safest solution. You can grant DDL permission to users, but that means they can execute any DDL including dropping other tables. This would be a serious breach of security, IMHO.

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[SemperFiDownUnda]

It doesn't matter if the tables being created are temp tables or not.

Users are granted exec permissions on a stored proc. Stored procs always run as DBO. The security model is as follows. If you want to give a user access to a storeproc the stored proc should be allow to do the full job it is intended to do without worring about all the permissions the user has.

So when writing stored procs all you have to think about is doing the task at hand and not what user at the end will be executing it. A stored proc may have Joe Bloggs granted Exec permission on it. The stored proc can create tables, by default, because it run as DBO as far as security goes. This doesn't mean Joe Bloggs now has rights to create tables.

If a stored proc calls other stored procs you don't have to worry about giving EXEC permissions to the Joe Bloggs for the lower stored procs either. Just give him access to the interface SPs.

I've seen whole databases where the users have had no permissions on the tables and everything was done via a few SPs and no permission changes where automated.



Hope I've been helpful,
Wayne Francis

If you want to get the best response to a question, please check out FAQ222-2244 first

 

[dafdba]

If an object which is called by a stored procedure, not exists at the moment the stored procedure is created, this object wil not be added in the sysdepends table. In that case you won't have permissions on that object when you run the stored procedure.