Microsoft.ReportingServices.Diagnostics.Utilities.AccessDeniedException

Posted on 26/11/2008 by Alistair Hunt

The permissions granted to user ‘DOMAINusername’ are insufficient for performing this operation. (rsAccessDenied) .

This error had been bugging me for a few hours and it occurs in numerous areas of SSRS so tracking down the problem was a pain. Here was my specific problem;

I had a domain user that only had permissions to see reports from one particular folder (and sub-folders) on the Report Server.
The user had been assigned the Content Manager role and could upload and run reports.
The problem ocurred when the user wanted to edit the properties of the report and then they would get the above error.

After a bit of digging around it seems that if your user does not have any permissions at the root level (Home folder) then they are not allowed to change any of the settings within a report, regardless of their current role assignment. In this case the solution was straight forward. I just added my domain user to the root folder with a Browser role. Since my root folder security settings were not propogating to any sub folders the user only has permission to access their particular reports and could now amend the properties for them as well.

For more information on the above error, take a look at;

http://msdn.microsoft.com/en-us/library/aa337279(SQL.90).aspx – rsAccessedDenied (SQL Server Books Online 2005)
http://msdn.microsoft.com/en-us/library/ms156014(SQL.90).aspx – Managing Permissions and Security for Reporting Services

Simultaneous Lookups using Full Cache to the same table

Posted on 21/11/2008 by Alistair Hunt

Creating multiple simultaneous Lookups to the same table that use Full cache.

Whilst developing a DTSX package for my ETL process I came across an issue that had me stumped for a while. If you have multiple Lookup Data Flow items in your packge that perform lookups to the same table then you may encounter a situation where the second lookup to a table generates an error in the package.

In the package (above) my calls to to the Lookup table (Segment 2(2) & Segment 3(2)) are made simultaneously and using identical code, however the second request fails with the following error;

A quick bit of research indicated that caching may have been the issue here – https://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=4010917&SiteID=17 – but I have found an alternative solution to the problem that allows you to keep your caching set to Full. If you change your Lookup Data Flow item such that the lookup columns are defined in a SQL Query and you include a unique alias for the tablename, you can then lookup values in the same table multiple times in the same package using Full cache mode.

Its more straight forward than you think.

Posted on 07/11/2008 by Alistair Hunt

Passing MultiValue paramaters to a Stored Procedure in Reporting Services.

I have been working on a Reporting Services report that contained a MultiValue select list as one of the parameters and I needed to pass this comma separated list of values to my stored procedure. I had prepared my stored procedure so that it contained a user defined function to split out the values into a table and had tested my stored procedure independently of the report. My problems occurred when I tried to run my report and select multiple from my list. I would end up with the following error – ‘must declare scalar variable @VarName’. To confuse things a little further, if I just selected one item from the MultiValue list then my report would run fine.

The answer, according to other enlightend ssrs users, comes from the method used to define your dataset. I had originally defined my dataset using the Text Command Type to call the stored procedure. E.g. EXEC myStoredProc @MultiValueParam. To get stored procedures to use the multivalue paramaters the dataset needs to be created with a Stored Procedure Command Type and then define the parameters within the Parameters tab.

After making the change my report would run successfully using my multivalue prameters.