SQL Server : create table within stored procedure

Question

I have a stored procedure that will be run by a user with public permissions. When operational, it will be called from an Excel spreadsheet and the results presented on a given tab.

The procedure has many execution paths, one of which is to create a table - [temp].[xyz] (temp already exists as a schema - users with 'public' have alter permission on this schema)

This same s/p is later called many times and selects various results from [temp].[xyz]

Finally, the s/p is called to drop [temp].[xyz]

However, the problem starts when it tries to create [temp].[xyz] using

select ... 
into [temp].[xyz]

Error: I don't have permission to create a table. (Note: The table does not exist already)

I think the solution may be something along the lines of : a sys admin creating a new login who has table create permissions. When it comes to the create(select into) table, wrap this statement in an 'EXECUTE AS' statement this the login who has create privs.

I'm OK with SQL but very basic on sys admin. If you need more details, please ask. Any help as to how I can get round this is appreciated.

Thanks, J

Answer 1

My answer here is based on this one statement:

Error: I don't have permission to create a table.

You can use EXECUTE AS in your stored procedure to give it permissions that the person calling it doesn't have. This should be used with caution as permissions are granted with good reason. For example if you create a user called TableCreator and grant it the ability to create tables in the temp schema.

GRANT CREATE TABLE TO TableCreator 
GRANT ALTER ON schema::temp TO TableCreator

Then create your stored procedure with the following:

CREATE PROCEDURE myProc
WITH EXECUTE AS 'TableCreator'
AS 
...

Now any time that stored procedure is run it will run as the user TableCreator which will have the ability to create, alter and drop tables within the schema temp.

Answer 2

You can add this before the select ... into ...:

If Exists(Select 1 From INFORMATION_SCHEMA.TABLES Where TABLE_NAME = 'xyz' and TABLE_SCHEMA = 'test')
Begin
    EXEC sp_executesql N'Drop Table test.xyz'
End

It will drop the table if it already exists before the select into try to create it.