Database Ownership

Dear Support
Upon knowing the cross database chaining option in SP3 on SQL2000 Server, I finally understood why I had troubles on our applications last year. I took a 'giant' step to work around the issue last time and it is about time I should make it right now. I am hoping if you could share some thought and have your comments on the following live scenario
1. I have 3 databases(say A,B and C) and they are required together to serve 3 applications or 3 login users through Access's ADPs(say a,b and c). Each frontend application is designed and programmed to update only on its own database, but they are allowed to pull data from the other two databases. For example, 'a' could read/write on A database, but readonly on B and C database
My SQL Server is in Windows Authentication mode. Say, if I make changes to three users(again a,b,c) of their database access setting on database A,B and C by declaring all of them(a,b,c) to be the owner (dbo) of all three databases, my first question is, can I declare database ownership on more than one users, or say, can one database or its objects be owned by more than one login?
Second, from a performance standpoint, there will be no 'broken link' if I am correct, and may I assume the response time will be better to users?
Third, if all a,b,c users are all database owners, or say 'a' owns A,B,C database, and so as 'b' also owns A,B,C database, am I correct that I will lose the capability to fine tuning the permission setting on database objects (such as stored procedure exec., r/w on tables/fields) at database level on each database
I know I should stop here but the cross database chaining concept is getting very interesting to me as a DBA/Programmer and the scenario I brought up her is all I am facing in my shop. I hope you could pardon me by allowing me to continue bring up the following of my concerns
If, say, I decide to integrate all three (a,b,c) applications into one, say BigBoy, this new BigBoy will have read and write functions/buttons on all A,B and C database. Now, the original users of a,b,c are now using only one application, the BigBoy. If I want to fine tuning the read and write permissions on databases without relying on the fronend applications, am I correct to remove the dbowner role from each of the login of the a,b,c user, and use/click the select, update,exec, etc. on the object list from the permission screen for each user
2. May I assume that the three users(a,b,c) I refer to above, can be replaced by or applied to Window's user defined group?
3. My orginal intention is to use Role instead of group for setting the new permission scheme, but I was told the Role can not span across databases. Would you confirm on this, or if there is a workaround on Role? The reason I try to use Role because my shop has Network administration personnel and I could separate security tasks between Network Admin and DB Admin by using user defined DB Role
Thank you for looking into this matter
MartinAnswered in security. Please don't post the same question independently to
multiple groups.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Martin" <anonymous@.discussions.microsoft.com> wrote in message
news:6B0EF357-1938-437E-886D-1614BF004B5E@.microsoft.com...
> Dear Support,
> Upon knowing the cross database chaining option in SP3 on SQL2000 Server,
I finally understood why I had troubles on our applications last year. I
took a 'giant' step to work around the issue last time and it is about time
I should make it right now. I am hoping if you could share some thought and
have your comments on the following live scenario.
> 1. I have 3 databases(say A,B and C) and they are required together to
serve 3 applications or 3 login users through Access's ADPs(say a,b and c).
Each frontend application is designed and programmed to update only on its
own database, but they are allowed to pull data from the other two
databases. For example, 'a' could read/write on A database, but readonly on
B and C database.
> My SQL Server is in Windows Authentication mode. Say, if I make changes
to three users(again a,b,c) of their database access setting on database A,B
and C by declaring all of them(a,b,c) to be the owner (dbo) of all three
databases, my first question is, can I declare database ownership on more
than one users, or say, can one database or its objects be owned by more
than one login?
> Second, from a performance standpoint, there will be no 'broken link' if I
am correct, and may I assume the response time will be better to users?
> Third, if all a,b,c users are all database owners, or say 'a' owns A,B,C
database, and so as 'b' also owns A,B,C database, am I correct that I will
lose the capability to fine tuning the permission setting on database
objects (such as stored procedure exec., r/w on tables/fields) at database
level on each database?
> I know I should stop here but the cross database chaining concept is
getting very interesting to me as a DBA/Programmer and the scenario I
brought up her is all I am facing in my shop. I hope you could pardon me by
allowing me to continue bring up the following of my concerns:
> If, say, I decide to integrate all three (a,b,c) applications into one,
say BigBoy, this new BigBoy will have read and write functions/buttons on
all A,B and C database. Now, the original users of a,b,c are now using only
one application, the BigBoy. If I want to fine tuning the read and write
permissions on databases without relying on the fronend applications, am I
correct to remove the dbowner role from each of the login of the a,b,c user,
and use/click the select, update,exec, etc. on the object list from the
permission screen for each user?
> 2. May I assume that the three users(a,b,c) I refer to above, can be
replaced by or applied to Window's user defined group?
> 3. My orginal intention is to use Role instead of group for setting the
new permission scheme, but I was told the Role can not span across
databases. Would you confirm on this, or if there is a workaround on Role?
The reason I try to use Role because my shop has Network administration
personnel and I could separate security tasks between Network Admin and DB
Admin by using user defined DB Role.
> Thank you for looking into this matter.
> Martin