2

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

4
  • 4
    I'm not sure I understand what the problem is? Are you getting an error when you run the procedure? If so what? Aug 31, 2015 at 16:58
  • Can you just use the tempdb instead of creating a temporary table in the actual database? Try changing out [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.
    – Aaron
    Sep 1, 2015 at 0:00
  • I have a very rough idea about what's going on, but this shouldn't be a guessing game for us. It would be best if you just described a specific scenario in which the problem occurs, step by step. Remember that we know nothing about the issue apart from you've told us.
    – Andriy M
    Sep 1, 2015 at 9:02
  • A temporary table only exists when the session stays active. The call from VBA to create the table does not hold onto the session. Sep 2, 2015 at 15:15

2 Answers 2

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.

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.