Post reply

Getting Into Permission Issue in SQL Server Reporting Services 2005

  • February 25, 2009 at 9:53 am

    #206336

    Hello,

    Getting Into Permission Issue in SQL Server Reporting Services 2005

    I created a report using Business Intelligence Studio and deployed it on server.

    I used the connection string like "server=servername;uid=username;pwd=passwd;database=databasename

    I can see the report fine. But one of the user is having issues seeing the report.

    I gave the user permission from the Report Manager. When I give the user permission at the database level then the user can see the report but If I take out the user from the database then the user cannot see the report and get the login error.

    Now does that mean that I have to give user permissions to all the users who will access this report. If that is the case then it is very discouraging as I cannot add all the users at the database level.

    Kindly Suggest. Thanks

  • February 27, 2009 at 6:47 am

    #951124

    You can centralize your permissions using a shared data source. Others may have a more elegant way of accomplishing this, but here's how we do it:

    1. Create a user in the server (ours is named "ReportingUser") that has permissions needed for reporting (i.e. read access, execution permissions for stored procs, etc)

    2. Create a directory in the reports site called "Shared Data Sources"

    3. Create a new data source in that directory that connects to the database from which you pull data.

    4. The new data source should connect to the proper server/database with the user created in step one

    5. Each new report can then be assigned to use the shared data source to connect to the data

    6. Then you set up user report access security in Reporting Services (we use AD groups for easier maintenance)

    This allows you to separate the report access security from the data access security the reports need.

    I'm not sure exactly how IIS should be set up for this (maybe others can help), but I know it needs to use some sort of user based security.

    Hope this helps.

  • February 27, 2009 at 7:12 am

    #951146

    thanks a lot DAN

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply