CODEWITHSUNDEEP
sql-serverstored-proceduresreporting-services
user38858
user38858

Reputation: 316

How can I set up the SQL Server permissions to make SSRS utilize a stored procedure rather than an embedded query?

I'm trying to set up a SSRS report using a query inside a stored procedure. I previously had the query embedded in the report, but I wanted to use a stored procedure instead for various reasons.

All of the SSRS reports are run from a SQL Server user named reporting.

My stored procedure name is proc, and the schema name is reports. For the user called reporting, and granted that user permission to select and execute. (I think. See screen shot.)

I can run the stored procedure when I use my full rights account (to confirm that it actually works), and the report works inside the Visual Studio 2012.

When I upload the report onto the reporting server, then attempt to access it through the web browser, I get the following error message:

The report server cannot process the report. The data source connection information has been deleted. (rsInvalidDataSourceReference)

Can anyone give me a hint on how I can make the report appear as intended?

Thank you.

Upvotes: 0

Views: 1255

Answers (1)

benjamin moskovits
benjamin moskovits

Reputation: 5458

When you uploaded the report it lost the rights you have on the proc. You have to enter a user in the Report's Data Source that has execute permission on the stored procedure (it could be you or anyone else as long as you know the password and it has the required rights).

Upvotes: 1

Related Questions