473,323 Members | 1,622 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,323 software developers and data experts.

Cross Database Select

Here's the thing.

We have a SQL Server with more than 25 Databases on it. Each database
is completely independant (but they all have the same structure) and
is use to manage different project.
We have more than 20000 logins split up between these databases
(average between 800 and 1200 users per database). When users connect
to the application, they have to specify the database they want to
connect to and based on this, a list of projects shows up for them to
choose from. In each database there's a table that contains the
user's login and the projects that he's allowed to see.

Now we want to centralize the way security is setup. It was decided
to merge each of the table to a new database (let's call it
DatabaseABC for now) using a trigger. The thing is that in each
database we need to replace the current view that reads from the table
to make it point to that new "Merged" table in DatabaseABC. I
probably need to say now that modification of the application code is
not an option.
As you can probably understand, we don't want to have to deal with
more than 20000 users in a single database. But we need any of the
users from any of the database to be able to select from their
respective views (meaning they need select access to the new merged
view in DatabaseABC).

We though about Cross database ownership, but you still need the user
to exist in DatabaseABC.
We then try to grant public with select on the table, but again you
still need the users to exists. Finally we created a Linked Server to
the server itself (loopback). This fix our problem of the 20000
users, but as you probably know, we have the "The operation could not
be performed because the OLE DB provider was unable to begin a
distributed transaction." because some code inside the application is
openning transaction.
It was suggested to create a merge replication between the DatabaseABC
and each of the 25 other databases, but I realy but really don't want
to get to that point. It would be a real pain to manage.
The last choice we have is to replicate the merged table from
DatabaseABC to another instance of SQL and pointing the views to that
new instance. It's not really nice, but it would work.

So here's the question...

Anybody has an other idea or any input that could be useful? Any way
of selecting from the merged table without having to replicate it
outside the instance?

Thanks in advance to all.
Jun 27 '08 #1
1 4427
(Pa*************@snclavalin.com) writes:
Anybody has an other idea or any input that could be useful? Any way
of selecting from the merged table without having to replicate it
outside the instance?
Did you try enabling the guest user? I think that should work, but I
don't think it is the best of ideas. But if you are on SQL 2000, this is
about your only option, I think.

If you are on SQL 2005, this might be doable with a combination of
impersonation and certificate signing. But it depends a little on how
the data is accessed. It sounds from your post as if the application
submits the queries directly. Had you used stored procedures, you could
have signed these. I don't think it helps to sign a view, but you would
need a minimum of a multi-statement function. But to query a function,
you need to have the () in the query, which would affect the application.

In any case, you could have a look at my article about granting
permissions to stored procedures on http://www.sommarskog.se/grantperm.html
and see if you get any ideas.

If that doesn't work out, I would probably bite the bullet, and all
20000 of them to the database.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jun 27 '08 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Web Science | last post by:
Site and Features: http://www.eigensearch.com Search engine, eigenMethod, eigenvector, mathematical, manifolds, science, technical, search tools, eigenmath, Jacobian, quantum, mechanics,...
0
by: Eric Paul | last post by:
I hope someone can offer me some advice or additional areas to search for the answer. I'm trying to do a cross database join on a single server. My searches indicate that the way to do this is a...
1
by: Tim Pascoe | last post by:
I am using the Dynamic Cross-Tab code supplied in an article from SQL Server Magazine (http://www.winnetmag.com/SQLServer/Article/ArticleID/15608/15608.html). I modified the script to generate a...
1
by: Joseph Barron | last post by:
Here is a SIMPLE problem that I'm trying to solve. It works in Netscape 6.2, but IE6 gives ""No such interface supported." Below are page1.htm and page2.htm . In page1.htm, there are two...
0
by: Web Science | last post by:
Site and Features: http://www.eigensearch.com Search engine, eigenMethod, eigenvector, mathematical, manifolds, science, technical, search tools, eigenmath, Jacobian, quantum, mechanics,...
0
by: Web Science | last post by:
Site and Features: http://www.eigensearch.com Search engine, eigenMethod, eigenvector, mathematical, manifolds, science, technical, search tools, eigenmath, Jacobian, quantum, mechanics,...
10
by: aaronrm | last post by:
I have a real simple cross-tab query that I am trying to sum on as the action but I am getting the "data type mismatch criteria expression" error. About three queries up the food chain from this...
0
by: dsithoo | last post by:
Hi, I have a stored procedure on database A (owner dbo) which is itself owned by dbo. It tries to do a SELECT from a table in database B (both table and db owned by dbo) but fails with: "Msg...
0
by: ElArZ | last post by:
Hi, I have a VB.NET application built with .Net 2.0 which is a call accounting software. I have created an asynchronous tcp connection to the PABX. Every time i receive data from the PABX, an...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.