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 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
Finally, the s/p is called to drop
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.