question

devoej avatar image
devoej asked

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!
error messageuser rights
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KenJ avatar image
KenJ answered
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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

devoej avatar image
devoej answered
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!
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

It won't work the way you want in a view. The TRY CATCH approach I outlined should give you the results you are looking for but it would need to be inside a stored procedure. If it has to be a view, you will have to pull all the data together into a single database and implement user based filtering (a user user id lookup table that you can inner join to the actual data from the source databases)
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.