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.
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.
Comment