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
[temp].[xyz]
with just#xyz
. If you need it to be global because you are calling other procedures from within the first procedure you can make it global by using##xyz
. But be careful with the global tempdb tables as they are visible by all processes and errors will occur if more than one instance is calling the main procedure at a time as well as they could manipulate the data already in the table or inject other rows that are not from the calling procedure.