Drill Down Reports Permissions Problem

Hi,
I've successfully setup the reports server 2005, on Windows Server 2003, and
had the permissions for the specific users configured to enable access to
the report. When a user logs into the report server, they can access the
reports I've created and deployed.
The problem arises when they try to drill down to the next level of the
report. I've created a report that is a matrix/crosstab report in the report
builder and the user can see the default (top level) of it without a problem.
However, as soon as they click on a record to drill down and get details on
it, they get an rsAccessDenied error that DOMAIN\User does not have
permissions.
As a test, I gave a specific user full content-manager rights (essentially I
tried everything short of giving them local admin rights to the box) and they
still can't run the drill down.
I'm wondering if they need report builder installed to generate the drill
down on the fly? We are using IWA on the domain to access the report server
itself. I thought I had the permissions set up fine, since they can run the
report at first, and I haven't found any options to allow/disallow drill
downs.
Any suggestions to try would be much appreciated. Thanks in advance for the
help.
-PeteHello Pete,
I would like to know this issue more clearly.
1. Did you mean you create the report in the report builder?
2. Have you provided proper permission on the Report Model you use in the
report builder?
3. Did this issue appeared on all the report you developed in report
builder?
4. Have you applied the latest services pack of SQL Server?
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Wei,
Thanks for the quick response. I'll answer your questions in-lin below, and
I appreciate any help you can give.
1. Did you mean you create the report in the report builder?
Yes, I created the data source, and data model using the report manager, and
then created the report using report builder. I did all of this as the local
admin on the box on which reporting services was running, and had no
permissions problems (as I was in the default group on RS). The user was on
an external WinXP bx, without report builder loaded when trying to access the
report. They were manually added in as a Content Manager User on Report
Server, at the top level (which I believe gives them rights to all the lower
levels like th report and model.)
2. Have you provided proper permission on the Report Model you use in the
report builder?
Yes, I did. They are a Content Manager role user at the top level. They
can see the model, as well as the top level of the report.
3. Did this issue appeared on all the report you developed in report
builder?
It only appeared in Cross-tab (matrix) reports that I created. Standard
reports that do not have drill down capabilities ran fine.
4. Have you applied the latest services pack of SQL Server?
No, I haven't. I don't believe they have been approved by internal IT
support yet, but I can check if necessary. Would you recommend I install
just SP1, or the additional hot fixes that are currently available after SP1
as well?
Thank you for the help. Please let me know if you need more information,
and I'd be happy to provide it.
-Pete
"Wei Lu [MSFT]" wrote:
> Hello Pete,
> I would like to know this issue more clearly.
> 1. Did you mean you create the report in the report builder?
> 2. Have you provided proper permission on the Report Model you use in the
> report builder?
> 3. Did this issue appeared on all the report you developed in report
> builder?
> 4. Have you applied the latest services pack of SQL Server?
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ==================================================> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>|||Hello Pete,
Thanks for the update.
First, please apply the SP1 for SQL 2005 and let me know the result.
Second, I would like to know whether you have grant the proper permission
on the datasource for the user.
That means, did the user have permission to access the datasource?
If the datasource is the sql server, did they have permission to access it?
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Wei,
I checked the user account, and he does have permissions to the data source
- he is able to edit or modify it, and generate a new model if he wanted to.
As I mentioned, the SP1 has not been approved yet for deployment internally.
Is there a knowledge base article that shows how this solves the problem
perhaps that I can reference to attempt to push the addition of it through?
If not, are there any other options or thoughts? Thanks for the help - I
appreciate it. Please let me know if you need more information.
-Pete
"Wei Lu [MSFT]" wrote:
> Hello Pete,
> Thanks for the update.
> First, please apply the SP1 for SQL 2005 and let me know the result.
> Second, I would like to know whether you have grant the proper permission
> on the datasource for the user.
> That means, did the user have permission to access the datasource?
> If the datasource is the sql server, did they have permission to access it?
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hello Pete,
There is no KB about this issue.
When I ask you to check the permission , I mean you need to check whether
the user could access the SQL Server, not whether he could modify the data
source file in the report manager.
Also, could you generate a sample report so that I may try to reproduce
this on my side?
Sincerely yours,
Wei Lu
Microsoft Online Partner Support
=====================================================
PLEASE NOTE: The partner managed newsgroups are provided to assist with
break/fix
issues and simple how to questions.
We also love to hear your product feedback!
Let us know what you think by posting
- from the web interface: Partner Feedback
- from your newsreader: microsoft.private.directaccess.partnerfeedback.
We look forward to hearing from you!
======================================================When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================.|||Wei,
Thanks for the information. The data source is created using credentials
stored on the report server, which can successfully access the database. We
have one user to access the DB, and don't impersonate the one making the
request.
As for a sample report, I'm not sure can provide that easily. Due to the
environment where this is deployed, we can't let out any data or reports. I
could potentially create a similar one, but that would be on a different
database (Northwind?) and reports server.
The steps to create one are pretty easy, since i am not using ANY advanced
configuration. It is built only with the web-based report builder, and
contains two tables, in a matrix, or cross-tab, report.
Thanks for the help - any other ideas you may have would be appreciated.
-Pete
"Wei Lu [MSFT]" wrote:
> Hello Pete,
> There is no KB about this issue.
> When I ask you to check the permission , I mean you need to check whether
> the user could access the SQL Server, not whether he could modify the data
> source file in the report manager.
> Also, could you generate a sample report so that I may try to reproduce
> this on my side?
> Sincerely yours,
> Wei Lu
> Microsoft Online Partner Support
> =====================================================> PLEASE NOTE: The partner managed newsgroups are provided to assist with
> break/fix
> issues and simple how to questions.
> We also love to hear your product feedback!
> Let us know what you think by posting
> - from the web interface: Partner Feedback
> - from your newsreader: microsoft.private.directaccess.partnerfeedback.
> We look forward to hearing from you!
> ======================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others
> may learn and benefit from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
> ======================================================.
>
>|||Hello Pete,
The reason why I need a sample report is that I need to know the model you
created and the report design.
So any simple report which could reproduce the issue may help me to
troubleshooting.
Thank you!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)