Wednesday, January 25, 2012

Untrusted foreign keys in Reporting Services

This week one of my colleagues, Janos Berke mentioned a script from Brent Ozar. Since I have a quite fresh sandbox of SQL Server 2008 R2, I became curious what the takeover script will find.
This instance is on a dev box, so SSDE, SSAS, SSIS, and SSRS are installed as well.
The result wasn't shocking, however it was very interesting.
The ReportServer and ReportServerTempDB databases of SSRS contain untrusted foreign keys right after installation.

It is not a big issue, you might think. You might be right. But the fact that the result of Brent Ozar's script includes issues regarding your SSRS databases is very annoying :)
It's easy to correct the issue... Just an example:

ALTER TABLE [ReportServer].[dbo].[Catalog] WITH CHECK CHECK CONSTRAINT [FK_Catalog_ParentID]

..., etc. I hope you can fabricate the rest. (The double "CHECK" is not a typo.)

I reported this on Connect - hopefully no cleanup will be needed in future releases.

...ah, all right, here is a script which will have a result with all necessary statements :)

SELECT 'ALTER TABLE [ReportServer].[' + s.name + '].[' + o.name + '] WITH CHECK CHECK CONSTRAINT [' + i.name + ']' AS Command FROM ReportServer.sys.foreign_keys i INNER JOIN ReportServer.sys.objects o ON i.parent_object_id = o.object_id INNER JOIN ReportServer.sys.schemas s ON o.schema_id = s.schema_id WHERE i.is_not_trusted = 1 AND i.is_not_for_replication = 0
UNION ALL
SELECT 'ALTER TABLE [ReportServerTempDB].[' + s.name + '].[' + o.name + '] WITH CHECK CHECK CONSTRAINT [' + i.name + ']' AS Command FROM ReportServerTempDB.sys.foreign_keys i INNER JOIN ReportServerTempDB.sys.objects o ON i.parent_object_id = o.object_id INNER JOIN ReportServerTempDB.sys.schemas s ON o.schema_id = s.schema_id WHERE i.is_not_trusted = 1 AND i.is_not_for_replication = 0

2 comments: