Share via

Is dbo right required to the report users in SSRS

Kebede, Kirubel 1 Reputation point
2020-10-13T14:27:08.82+00:00

Hi All,
I just migrated a few SSRS reports from SQL 2012 to SQL 2016 standard edition using cmd scripts. I have also matched all the accesses from the old to the new including the roles and permissions in the SSRS that includes the site setting and folder level rights.
But when testing, our test user could not access the report as usual. Then i granted him access to the database (data source) from SSMS and he started accessing the report with no issue. BUT, from the security point of view, we don't want to grant dbo rights to report users. so my question is,

  • Is there a new requirement for SQL 2016 reporting ?
  • is there anywhere that i missed configuring?

I will be happy to answer your questions if its not clear.

Thanks
Kiru

SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,878 questions
0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2020-10-13T20:26:17.907+00:00

    Nothing should have changed, the user should have at least db_datareader permissions on any views / tables. Also check, in the case of stored procedure source, that user has execute permissions.

    --please don't forget to Accept as answer if the reply is helpful--

    0 comments
  2. Hilary Cotter 1 Reputation point
    2020-10-14T00:45:21.737+00:00

    the user may need to execute stored procedures, assuming your data source needs to execute them. The user will need rights to execute each stored procedure the report he runs uses.

    0 comments
  3. ZoeHui-MSFT 35,556 Reputation points
    2020-10-14T03:22:42.983+00:00

    Hi @Kebede, Kirubel ,

    If the reports are based on stored procedures, you might try specifically allowing those users execute permissions on the stored procedures.

    Example: 

    GRANT EXECUTE ON spName TO ReportingGroup;  
GO

    As you mentioned, you have migrated a few SSRS reports from SQL 2012 to SQL 2016.

    If you move a report server database to a new or different SQL Server Database Engine, you must re-create the RSExecRole in the Master and MSDB system databases.

    For reference:

    https://learn.microsoft.com/en-us/sql/reporting-services/security/create-the-rsexecrole?view=sql-server-ver15

    Regards，

    Zoe

    If the answer is helpful, please click "Accept Answer" and upvote it.

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.