Database Ownership

From: Martin (anonymous_at_discussions.microsoft.com)
Date: 01/15/04 

Date: Thu, 15 Jan 2004 13:21:22 -0800

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