March 19, 2013 at 10:41 am
On a SQL Server 2008 R2 Enterprise SP1 clustered instance these are the databases:
- Prod
- ProdReports
ProdReports as a view named View1 that looks like this:
SELECT r1.fields, p1.fields
FROM dbo.ReportingTable r1
INNER JOIN Prod.dbo.ProdTable t1 ON t1.something = r1.something
There is a user named ReportUser that is dbo for the ProdReports database and is in the db_datareader, db_denydatawriter roles for the Prod database.
There is a deployed SSRS report that uses the view listed above.
We needed to move the Prod database to new disks attached to cluster. To limit the downtime of the Prod database, we did the following:
1 – Backed up the Prod database.
2 – Restored the backup to the Prod_Restore database on the new disks.
3 – Shut down the applications and connections to the Prod database.
4 – Backed up the transaction log of the Prod database.
5 – Renamed the Prod database to Prod_DONOTUSE
6 – Restored the transaction log to the Prod_Restore database.
7 – Renamed Prod_Restore to Prod
8 – Removed all login mapping to the Prod_DONOTUSE database (including the ProdReports login).
9 – Ensured all login mappings to the Prod database are correct.
10 – Started application services.
All applications can connect successfully. Connections can be made via SSMS successfully.
Logged into SSMS using the ReportUser login and executed View1 without any problems.
On the SSRS website (which is hosted on a different server), the report does execute. When reviewing the SSRS logs I see the following error:
System.Data.SqlClient.SqlException: The server principal "ReportUser" is not able to access the database "Prod_DONOTUSE" under the current security context.
The same error occurs when attempting to execute the report in BIDS from my local computer.
This is what I have done so far:
- Deleted the ReportUser database user in all database and server login and recreated.
- Restarted SSRS
- Restarted local computer
- Created a different login with appropriate permissions to Prod and ProdReports – GOT THE SAME ERROR!
- Removed shared Data Source and recreated (both locally and on SSRS server)
- Created new SSRS project, new login, new Data Source, new report. SAME ERROR
- Cried
- Prayed
- Considered submitting my resume to the local janitorial services company.
None of the above corrected the issue.
Any suggestions?
...
March 19, 2013 at 11:32 am
Found the solution: ALTER VIEW on every view referenced by a data source in SSRS. Have no idea why this works, but it does work.
...
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply