SEO

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.

(Patrick.Laprise@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, esquel@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