Writing a view to query multiple databases on same server (rights issue)
I am needing to know if their is a good solution to the following problem: I need to create a view in one database that will query data from many other databases on the same server. As an example, the view would be created in database DB_Main and would query data from databases DB_SUB1, DB_SUB2, and DB_SUB3. The data structure of each of the SUB databases are identical to each other. I know I can query the SUB databases in my view as follows: SELECT Column1, Column2, Column3 FROM DB_SUB1.dbo.TableName UNION ALL SELECT Column1, Column2, Column3 FROM DB_SUB2.dbo.TableName UNION ALL SELECT Column1, Column2, Column3 FROM DB_SUB3.dbo.TableName The question I have has to do with user rights. If a certain user on this SQL server instance only has rights to DB_Main, DB_SUB1, and DB_SUB2, but not to DB_SUB3, will the view error when they try to run it? If the view will error, is there any way to get the view to run without errorring and only return data from the 2 SUB databases that the user has rights to? If you need more details, please let me know. Thanks in advance!
This feels so wrong. What is the latency requirement? Can the data be bundled together periodically using SSIS so you can query it from one place? Maybe it could be replicated into a single database and access controlled there? Anyway, I think the cross-database approach could work out something like this: Instead of doing `UNION ALL` to get all of your results together, you can create a temporary table that holds interim results, then execute each cross-database select within its own `TRY CATCH` block. Queries that don't have permission will be caught in the `CATCH` block and rolled back while queries that don't have permission problems will be able to insert rows into the temporary table. Once all of the inserts are complete (and errors handled), you can query the data out from the temporary table. This is ugly and won't compile, but should do the trick (I don't think permission errors close the user connection, so they should transfer control to the `CATCH`): CREATE TABLE #temporaryholding(column1 int, column2 int, column3 int) TRY INSERT #temporaryholding (column1, column2, column3) SELECT column1, column2, column3 FROM DB_SUB1.dbo.TableName CATCH -- got a permission error. ignore it and/or rollback TRY INSERT #temporaryholding (column1, column2, column3) SELECT column1, column2, column3 FROM DB_DUB2.dbo.TableName CATCH -- got a permission error. ignore it and/or rollback etc... SELECT column1, column2, column3 FROM #temporaryholding
Thanks for the replies. My main problem is that different users have rights to different SUB databases, though they all have rights to the MAIN database. One user may only have rights to DB_SUB1, but another user has rights to all the databases. Another user may have rights to DB_SUB1 and DB_SUB3. There are actually 14 SUB databases and each user could have rights to any combination of these databases. In the end, I would like for each end user to be able to run a query using the one view in the MAIN database and only see data that is coming from the SUB databases they have access to (with no error messages, of course). Thanks!