EXECUTE permission denied on Stored Procedure

[bluecjh]

SQL 2005.

rsErrorExecutingCommand

The stored procedure recides on a separate server from
the Reporting Services server.

"EXECUTE permission denied on object usp...."
(a stored proc)

I am Administrator on both. According to the data source,
contact is made to the database with windows integrated
security so am suprised to get this message?

can anyone help?... many thanks


BlueCJH

 

[mrdenny]

Odds are the report is logging into the database as the account which the service is running under, not under your account.

Check the data source and see which account it is configured to use. You can also fire up SQL Profiler and see what account is trying to run the stored procedure.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[bluecjh]

Iteresting, profiler identified

NT AUTHORITY\ANONYMOUS LOGON

as the culprit... so now i've either got to
give this account rights or get windows authentication working.

So i'm a step further, which is good news... i'm off
to have a look at IIS.

many thanks

BlueCJH

 

[Crowley16]

yeah, ssrs has a LOT of different points for security which can make it a pain in the proverbial at times to get the security right.

Things to check:

1) If you're using 2 servers, are they on the same network? Local accounts do not span...
2) The report can use either a custom connection or a shared connection, make sure you check the relevant one.
3) IIS has it's own security settings, but this only allows/blocks access to the report sites.

there's a lot more that can go wrong, but these cover most of the common problems, if you're still having problems, post back with details...

--------------------
Procrastinate Now!