Is dbo right required to the report users in SSRS

Question

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

Answer 1

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--

Answer 2

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.

Answer 3

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.