Post reply

CREATE TABLE permission denied in database 'Reporting'

  • November 12, 2012 at 4:10 pm

    #393589

    I am calling a stored procedure from an SSRS report. My stored procedure declares a table variable

    DECLARE @dataset TABLE ...

    I then select data from this table variable at the end of the procedure.

    My SSRS report uses an embedded credential. When I run the report I get the error: CREATE TABLE permission denied in database 'Reporting'

    What's the minimum permission I can add to my credential to be able for it to call the procedure, create the table variable and retrieve the results...

    Thanks if you can help.

  • November 13, 2012 at 2:52 am

    #1558411

    Hi,

    Instead of giving additional rights to the user used for the report, you could change the way the SP is run by using EXECUTE AS [UserWithSufficientRights]

    In SQL Server 2005 and above, the creator of a procedure can declaratively set the execution context of the procedure by using the EXECUTE AS keyword in the CREATE PROCEDURE, FUNCTION, and TRIGGER statements. The execution context choices are:

    - EXECUTE AS CALLER - the caller of the procedure (no impersonation). This is the only pre-SQL Server 2005 behavior.

    - EXECUTE AS OWNER - the owner of the procedure.

    - EXECUTE AS SELF - the creator of the procedure.

    - EXECUTE AS 'username' - a specific user.

    So, from the above, you could create a specific user (with or without a login) and give the appropriate rights, then recompile your SP by using the "EXECUTE AS [thatuser]" DDL statement.

    Here's a good document (for SQLSERVER2012) giving you more details:

    http://download.microsoft.com/download/8/F/A/8FABACD7-803E-40FC-ADF8-355E7D218F4C/SQL_Server_2012_Security_Best_Practice_Whitepaper_Apr2012.docx

    HTH,

    B

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply