Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

CREATE TABLE permission denied in database 'Reporting' Expand / Collapse
Author
Message
Posted Monday, November 12, 2012 4:10 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 2:08 PM
Points: 653, Visits: 3,841
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.
Post #1383904
Posted Tuesday, November 13, 2012 2:52 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 7:14 AM
Points: 167, Visits: 682
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
Post #1383995
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse