I am developing SQL Server Reporting Services (SSRS) reports on SQL Server 2008 R2 and using Report Manager as a method to demonstrate and test reports. I am looking for a way to allow users of the same domain to connect to the Report Manager and run reports via a browser (not SharePoint) without letting the user have too much access to the data source. I currently have each user listed as db_owner for the database that the datasets and data source are associated with. I would like to limit this access and I have tried db_datareader but this level does not allow the user to run the reports and gives the user this error: “Cannot create a connection to data source 'DBname'. (rsErrorOpeningConnection)”.
My method of adding a user to The Report Manager site: I select the ‘Security’ tab under ‘Site Settings’ and then select ‘New Role Assignment’ adding the user as a ‘System User’. I then select ‘Folder Settings’ on the toolbar and again select ‘New Role Assignment’ adding the user as a ‘Browser’. I have tried adding a user as a ‘Content Manager’ but they still have the same error when it comes to the data source.
My method of adding a user to the data source: select new login from the Security tab for the server, add domain\username to ‘Login name:’, use Windows authentication and change the default database from master to the database that is the reports data source. I then select ‘User Mapping’ and put a check next to the database that is the data source. In the ‘Database role membership for: DBname’ section I choose db_owner and public is already selected. I have included screenshots below. My question is what ‘Database role membership’ can I use for SSRS and Report Manager that would not be as broad as db_owner and would have the best security? I have tried db_datareader but then the user cannot connect to the data source when they run the report.
I have researched this question but I have not found any details accept for adding the user as a db_owner as I described. MSDN acts as if the settings in Report Manager are all that you need to set for the user/report to have access to the data source. I have tried only using the Report Manager settings with both settings for a data source, shared and imbedded with no luck.
Thank you in advance