SQL Error When Running My "Find All Unused Packages" in SCCM 2007 Report Console Asked by: the_ratzenator I am trying to run an SCCM 2007 Report to find all unused Pacakges in my environment and I keep getting this error:
An error occurred when the report was run. The details are as follows: The SELECT permission was denied on the object 'vPkgStatusSummaryDistPts', database 'SMS_PAK', schema 'dbo'. Error Number: -2147217911 Source: Microsoft OLE DB Provider for SQL Server Native Error: 229
Here is the SQL code I got from here: http://blog.itminutes.net/?p=781
SELECT PackageID, Name, Version, Manufacturer, Language, Description, PkgSourcePath AS [Source Path], LastRefreshTime, (SELECT COUNT(PkgID) FROM vPkgStatusSummaryDistPts WHERE PkgID = v_Package.PackageID) AS [No of DP] FROM v_Package WHERE PackageID NOT IN (SELECT PackageID FROM v_Advertisement) AND PackageID NOT IN (SELECT ReferencePackageID FROM v_TaskSequenceReferencesInfo) AND PackageType = 0 ORDER BY Name
This runs in SQL Server Management Studio, but not in my SCCM 2007 Reporting console.
I checked and the "webreport_approle" does exist.
Any help will be much appreciated.