CREATE TABLE permission denied in database 'Reporting'

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » CREATE TABLE permission denied in database...

CREATE TABLE permission denied in database 'Reporting'

Rate Topic

Display Mode

Topic Options

Author

Message

Chrissy321

Posted Monday, November 12, 2012 4:10 PM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 5:03 PM
Points: 551, Visits: 2,986

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

bleroy

Posted Tuesday, November 13, 2012 2:52 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 4:59 PM
Points: 137, Visits: 546

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 »

Permissions