you'll run into permissions problems accessing, say 10 databases in a single query;

the calling user would need at least read permissions in every single database for the specific table(s) involved..

i think you'll need to create a procedure, featuring EXECUTE AS with a user that has read permissions in all those databases, and then grant execute to that procedure to the groups that contain the users that will be using this report.

even better would be using a certificate, which i have no experience doing, but is the preferred way for permissions issues like this, i think.

--the EXECUTE AS must be a user in the database(s)...not a login

CREATE procedure pr_CallBoostedSecurityProcess

WITH EXECUTE AS 'superman'

AS

BEGIN

SELECT ColumnList FROM db1.dbo.Table1 UNION ALL

SELECT ColumnList FROM db2.dbo.Table1 UNION ALL

SELECT ColumnList FROM db3.dbo.Table1 UNION ALL

SELECT ColumnList FROM db4.dbo.Table1

END