November 13, 2018 at 8:37 am
Hi,
Recently when the application team did a SSRS Report deployment and the report was not working because the SSRS login in the database didn't have EXECUTE permission.
All other reports were working fine but this particular report which was deployed in that release alone was not executing.
What confused me is why that login doesn't need EXECUTE permission for other reports which has a stored procedure running behind it.
What is the recommended process.?
When i have a application login or SSRS login created in the database ,should i give EXECUTE permission for that login.?
Please give suggestions.
November 13, 2018 at 8:50 am
srajeevan1989 - Tuesday, November 13, 2018 8:37 AMHi,Recently when the application team did a SSRS Report deployment and the report was not working because the SSRS login in the database didn't have EXECUTE permission.
All other reports were working fine but this particular report which was deployed in that release alone was not executing.
What confused me is why that login doesn't need EXECUTE permission for other reports which has a stored procedure running behind it.What is the recommended process.?
When i have a application login or SSRS login created in the database ,should i give EXECUTE permission for that login.?Please give suggestions.
Been a while since I looked at SSRS, but when you publish a report, do you get the option of choosing the context it runs under? That is, does it run in the context of the user who requested it, or some other named user?
If it runs in the context of the user requesting it, that user will need EXECUTE rights on any stored procs which drive the reports.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 13, 2018 at 8:57 am
Phil Parkin - Tuesday, November 13, 2018 8:50 AMBeen a while since I looked at SSRS, but when you publish a report, do you get the option of choosing the context it runs under? That is, does it run in the context of the user who requested it, or some other named user?If it runs in the context of the user requesting it, that user will need EXECUTE rights on any stored procs which drive the reports.
You have multiple options, that is one of them. Other options are to use a specific Login to run the report, or a shared data source (which can again use things like the current user's credentials, stored credentials, etc). I suspect that the report was deployed using a different option in the connection details, or uses a different data source to the other reports (which has different connection options).
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
November 13, 2018 at 8:58 am
Hi Phil,
I am not that familiar with deploying SSRS reports,but i know that there is a common/generic login in database for SSRS reports to run.All the user who runs the report from the application uses that common user.So that user say, SSRS_login is being used to run the stored procedures behind the reports.
November 13, 2018 at 9:03 am
Hi Thom A,
All the stored procedures used in the reports are from same database and all uses the same user/login SSRS_login.
When a user,user_A login into the application and runs a report from the application,how does that user's login affect the SSRS_login.
I am not sure how the permissions are affected/change from application side.
November 18, 2018 at 8:46 am
srajeevan1989 - Tuesday, November 13, 2018 9:03 AMHi Thom A,All the stored procedures used in the reports are from same database and all uses the same user/login SSRS_login.
When a user,user_A login into the application and runs a report from the application,how does that user's login affect the SSRS_login.
I am not sure how the permissions are affected/change from application side.
In SSDT - when you build your report you can define the data source as local to the report or as a shared data source. If you defined the data source as local - when deployed it will create and embed that data source in the report definition and will be utilized for that report only.
If you define the data source as a shared data source in your project - and that shared data source does not exist on the server, your deployment will create a new data source. Note: if your project is setup to overwrite data sources (which is not recommended) then your deployment would overwrite the shared data source with your configuration.
With that said - as long as you utilize a shared data source in your project that is the same name as the shared data source on the server - and your projects are set to not overwrite the data sources, then the shared data sources in your project can have whatever configuration works for you (windows auth, sql auth) and when deployed will use the shared data source on the server. The shared data source on the server will be configured with the appropriate access - could be windows auth, sql auth, impersonation, etc...
Finally - if you build your report with a local data source - then after deployment you can change the data source in SSRS to use the shared data source on the server.
Whichever method you use - the account that accesses the data must have permissions required to run the report. That will include EXECUTE on the procedure(s) and/or SELECT on specific tables - possibly insert/update/delete or more...depending on what is being performed to support the report.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply