Can report in SSRS 2005 but cannot run in Report Manager, receive SELECT permission was denied on the object 'TableName', database 'DATABASE', schema 'dbo'

Question

Can report in SSRS 2005 but cannot run in Report Manager, receive SELECT permission was denied on the object 'TableName', database 'DATABASE', schema 'dbo'

tammyberardi

SSC Veteran

Points: 251
More actions
September 3, 2009 at 3:38 pm

#225844

I'm able to design and deploy a report in Visual Studio, but when I try to run through Report Manager, http://localhost/ReportServer or
http://localhost/Reports, I receive the following message:
An error has occurred during report processing. (rsProcessingAborted)
Query execution failed for data set 'DataSet'. (rsErrorExecutingCommand)
The SELECT permission was denied on the object 'TableName', database 'DataBase', schema 'dbo'.
Any assistance or links to solutions is appreciated.
Tammy

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Gift Peddie SSC Guru Points: 73570 More actions · Answer 1

You need to create shared datasource with relevant permission to run the query and it cannot be SELECT permissions only. If you are not the admin then you need to talk to the admin.

Kind regards,
Gift Peddie

Animal Magic SSChampion Points: 13964 More actions · Answer 2

what authentication are you using in your data source?? And does the report fail for you when you try to run the report or just other users?

Also check that when you go to data sources the required data source is there, but i dont think this is your issue - im sure you get an error message saying that the required data source is not found.

I set all mine up using a user called RSUser and then give that user the required permissions on the tables/views/SP's to be able to run the report.

To stop everyone being able to run every report i put the reports in departmental folders which i then use windows security groups to allow browser access to those reports.

Marcin Gol Hall of Fame Points: 3314 More actions · Answer 3

As Animal Magic said - problem is in security setting in report's data source. This is quite complex but we can look at this in these way:

if you are maintaing security in your database based on windows authentication - choose integrated security

if you are dont like windows auth or need to give access to some other peaple too you can prepare a special login/user with appropriate permissions to run you report and use it in data source settings (the password will be stored securely).

Gift Peddie SSC Guru Points: 73570 More actions · Answer 4

An error has occurred during report processing. (rsProcessingAborted)
Query execution failed for data set 'DataSet'. (rsErrorExecutingCommand)
The SELECT permission was denied on the object 'TableName', database 'DataBase', schema 'dbo'.

The above was the original error I think I covered it because when last I checked object permissions like SELECT denied on Table and DBO schema is not related to type of authentication. The report is running with SELECT permissions which executes in the context of Asp.net in SQL Server 2005 and in 2008 runs in the context of SSRS runtime.

And there is nothing complicated about using SQL Authentication or Windows Authentication.

Kind regards,
Gift Peddie

Marcin Gol Hall of Fame Points: 3314 More actions · Answer 5

complex problem = security

- kerberos

- integrated security

- + linked servers ...

there is a lot of places when something could go wrong!

his problem means that the report is executed on a "bad user" (without some permissions) - so maybe hi is already using integrated security? who knows?

Gift Peddie SSC Guru Points: 73570 More actions · Answer 6

complex problem = security
- kerberos
- integrated security
- + linked servers ...
there is a lot of places when something could go wrong!
his problem means that the report is executed on a "bad user" (without some permissions) - so maybe hi is already using integrated security? who knows?

I know because I am a security expert, it is not related to Kerberos because there is nothing in the post related to double hop which generates kerberos errors.

And integrated sercurity is another name for Windows Authentication.

There is also no Linked Server in the error, when code runs within Visual Studio the error is very limited.

@ tammyberardi

Make sure the user of the account used to execute the code have permission in the database, table and DBO schema of that database. If that is not the issue then it is related to Report Manager permisisons those take a long time to resolve so post again if that is your problem.

Kind regards,
Gift Peddie

Marcin Gol Hall of Fame Points: 3314 More actions · Answer 7

The problem is that the "report user" doesn't have some of the required permissions. Default settings are integrated security(which means windows authentication - yes i know it too ;pp) - so he:

1) should change permissions in database

2) create special login/user with proper permissions to execute report

is it better for you? (this answer is very similar to my first post)

Gift Peddie SSC Guru Points: 73570 More actions · Answer 8

I know because I am a security expert, it is not related to Kerberos because there is nothing in the post related to double hop which generates kerberos errors.
And integrated sercurity is another name for Windows Authentication.
There is also no Linked Server in the error, when code runs within Visual Studio the error is very limited.
@ tammyberardi
Make sure the user of the account used to execute the code have permission in the database, table and DBO schema of that database. If that is not the issue then it is related to Report Manager permisisons those take a long time to resolve so post again if that is your problem.

So there is a difference between your post and mine in SSRS security I am not seeing it.

Kind regards,
Gift Peddie