SQL Server : create table within stored procedure 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
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.