Permissions granted are insufficient for this operation

Question

I am using Report Builder 3.0 and have been trying to run reports using AdventureWorks 2014 in SQL Server Express 2014. My report Server is http://John-PC/ReportServer_SQLExpress.

When I click Run in Report Builder I get a message saying The permissions granted to User ‘John-PC\John’ are insufficient for performing this operation.

I have confirmed that there is a login for John-PC\John and that it is assigned to user John-PC\John. I have tried to give permissions using, among others: exec sp_addrolemember 'db_owner', 'John-PC\John' which resulted in command completed successfully but not in changing the error message.

When I connect to SQL Server my own username is John-PC\John; I suppose that is related but my own permissions seem fine. I’ve been fighting this for about 3 weeks.

Can someone give me a different approach or correct the one I’m using so that I can run reports in Report Builder ? Thank you very much. John

Answer 1

Report builder works on different permissions than the raw SQL Server permissions which enable you to log into the SQLOS and execute code against the DB engine. Report builder needs you to identify users in other areas.

Microsoft has some documentation on the matter here. If you are running native mode (non sharepoint) you can check:

https://msdn.microsoft.com/en-us/library/ms365173.aspx

You will need to add users which can be found here on MS docs:

https://msdn.microsoft.com/en-us/library/ms156034.aspx

To add a user or group to a system role:

Start Report Manager (SSRS Native Mode).

Click Site Settings.

Click Security.

Click New Role Assignment.

In Group or user name, enter a Windows domain user or group account in this format: \. If you are using forms authentication or custom security, specify the user or group account in the format that is correct for your deployment.

Select a system role, and then click OK.

Roles are cumulative, so if you select both System Administrator and System User, a user or group will be able to perform the tasks in both roles. Repeat to create assignments for additional users or groups.