InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 18 May 2013 09:08:38 GMT20http://www.sqlservercentral.com/Forums/Topic1383904-392-1.aspxHi,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:[url]http://download.microsoft.com/download/8/F/A/8FABACD7-803E-40FC-ADF8-355E7D218F4C/SQL_Server_2012_Security_Best_Practice_Whitepaper_Apr2012.docx[/url]HTH,BTue, 13 Nov 2012 02:52:06 GMTbleroyhttp://www.sqlservercentral.com/Forums/Topic1383904-392-1.aspxI am calling a stored procedure from an SSRS report. My stored procedure declares a table variableDECLARE @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.Mon, 12 Nov 2012 16:10:48 GMTChrissy321