Our SQL Server DBA has left the company with many of the databases owned by his domain user. I understand that I could change database ownership using sp_changedbowner. Does anyone know what would be a better option, from security point of view?
1. Let databases owned by 'sa' like all system databases.
2. Let databases owner by the domain account that runs SQL Service.
3. Create a local/domain account which owns all databases on this SQL Server instance.
4. Let databases owned by Administrator account.
I am new to SQL Server. Help would be appreciated.Howdy
'sa' is the best way to go.
By all means, set up SQL & SQL Agent to run using a domain account unless it needs to be a highly secure box.
By the way, any account who is in the local Administrators group ( this is NT security I'm talking about ) on the box, is automatically sa ( potential LARGE security hole ).
Post back if any more questions. Happy to answer.
Cheers
SG|||Thanks for the info, will try that.
Showing posts with label ownership. Show all posts
Showing posts with label ownership. Show all posts
Tuesday, February 14, 2012
Database ownership best practice
Does it matter much who owns a database? I'm reviewing security on our
SQL installation and find a number of different owners: sa, Domain
administrators and some others. I want to tidy this up. Is it best to
make an NT administrator the owner of all databases, assign a special
user which does nothing else but own databases?
Having looked at Books Online and elsewhere, I don't see any advice as
broad based as what I'm seeking.
Any suggestions would be welcome.I think it's mostly an audit trail, to see who created the database.|||Hi
Our corporate standard is that all objects must be owned by SA. If it owned
by a domain user, or a SQL Server login, if that user leaves you have to
change the ownership of the object before you can remove the user from the
DB/system.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
<stevester@.freeuk.com> wrote in message
news:1123238913.304851.90500@.g44g2000cwa.googlegroups.com...
> Does it matter much who owns a database? I'm reviewing security on our
> SQL installation and find a number of different owners: sa, Domain
> administrators and some others. I want to tidy this up. Is it best to
> make an NT administrator the owner of all databases, assign a special
> user which does nothing else but own databases?
> Having looked at Books Online and elsewhere, I don't see any advice as
> broad based as what I'm seeking.
> Any suggestions would be welcome.
>|||Like Mike Epprecht said, it's a good practice to use a standard SQL login as
the database owner since this will ensure the database owner account is
never orphaned. An orphaned database owner account can have side affects,
such as problems using sp_helpdb or using Enterprise Manager. If only
sysadmin role members can create database objects on your SQL Server, 'sa'
database ownership is probably ok. However, I'd like to add some additional
notes on the importance of the database owner.
The login associated with the database owner determines the mapping of the
database's 'dbo' user. 'dbo' has full permissions within the database so
the database owner login account needs to be appropriately secured.
Furthermore, when cross-database chaining is enabled (configurable option in
SQL 2000 SP3+ and always on in older SPs/versions), dbo-owned views, procs,
functions and triggers can access dbo-owned objects in other databases owned
by the same login even without user permissions on the referenced objects.
This is a major security concern with sa-owned databases because master is
also owned by 'sa' and is why one should enable cross-database chaining in
an sa-owned database when only sysadmin role members can create dbo-owned
objects. It is best to avoid turning on cross-database chaining unless
absolutely necessary.
If non-sysadmin role members can create dbo-owned database objects and you
must enable cross-database chaining, you should instead consider creating
and using a specialized SQL login for database ownership. See the Books
Online for more information on cross-database chaining.
Hope this helps.
Dan Guzman
SQL Server MVP
<stevester@.freeuk.com> wrote in message
news:1123238913.304851.90500@.g44g2000cwa.googlegroups.com...
> Does it matter much who owns a database? I'm reviewing security on our
> SQL installation and find a number of different owners: sa, Domain
> administrators and some others. I want to tidy this up. Is it best to
> make an NT administrator the owner of all databases, assign a special
> user which does nothing else but own databases?
> Having looked at Books Online and elsewhere, I don't see any advice as
> broad based as what I'm seeking.
> Any suggestions would be welcome.
>|||The owner info is of limited use from an audit perspective because the
database owner can be subsequently changed. See my response on security
considerations.
Hope this helps.
Dan Guzman
SQL Server MVP
<MICHAEL_SUNLIN@.COUNTRYWIDE.COM> wrote in message
news:1123249058.756243.222770@.z14g2000cwz.googlegroups.com...
>I think it's mostly an audit trail, to see who created the database.
>|||Thanks for the advice. I liked the idea of a dedicated db owner, to
keep things modular, but the orphaned user concern is a good point.
I'll review those areas mentioned and go with sa as long as the
associated points are not a problem.
SQL installation and find a number of different owners: sa, Domain
administrators and some others. I want to tidy this up. Is it best to
make an NT administrator the owner of all databases, assign a special
user which does nothing else but own databases?
Having looked at Books Online and elsewhere, I don't see any advice as
broad based as what I'm seeking.
Any suggestions would be welcome.I think it's mostly an audit trail, to see who created the database.|||Hi
Our corporate standard is that all objects must be owned by SA. If it owned
by a domain user, or a SQL Server login, if that user leaves you have to
change the ownership of the object before you can remove the user from the
DB/system.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
<stevester@.freeuk.com> wrote in message
news:1123238913.304851.90500@.g44g2000cwa.googlegroups.com...
> Does it matter much who owns a database? I'm reviewing security on our
> SQL installation and find a number of different owners: sa, Domain
> administrators and some others. I want to tidy this up. Is it best to
> make an NT administrator the owner of all databases, assign a special
> user which does nothing else but own databases?
> Having looked at Books Online and elsewhere, I don't see any advice as
> broad based as what I'm seeking.
> Any suggestions would be welcome.
>|||Like Mike Epprecht said, it's a good practice to use a standard SQL login as
the database owner since this will ensure the database owner account is
never orphaned. An orphaned database owner account can have side affects,
such as problems using sp_helpdb or using Enterprise Manager. If only
sysadmin role members can create database objects on your SQL Server, 'sa'
database ownership is probably ok. However, I'd like to add some additional
notes on the importance of the database owner.
The login associated with the database owner determines the mapping of the
database's 'dbo' user. 'dbo' has full permissions within the database so
the database owner login account needs to be appropriately secured.
Furthermore, when cross-database chaining is enabled (configurable option in
SQL 2000 SP3+ and always on in older SPs/versions), dbo-owned views, procs,
functions and triggers can access dbo-owned objects in other databases owned
by the same login even without user permissions on the referenced objects.
This is a major security concern with sa-owned databases because master is
also owned by 'sa' and is why one should enable cross-database chaining in
an sa-owned database when only sysadmin role members can create dbo-owned
objects. It is best to avoid turning on cross-database chaining unless
absolutely necessary.
If non-sysadmin role members can create dbo-owned database objects and you
must enable cross-database chaining, you should instead consider creating
and using a specialized SQL login for database ownership. See the Books
Online for more information on cross-database chaining.
Hope this helps.
Dan Guzman
SQL Server MVP
<stevester@.freeuk.com> wrote in message
news:1123238913.304851.90500@.g44g2000cwa.googlegroups.com...
> Does it matter much who owns a database? I'm reviewing security on our
> SQL installation and find a number of different owners: sa, Domain
> administrators and some others. I want to tidy this up. Is it best to
> make an NT administrator the owner of all databases, assign a special
> user which does nothing else but own databases?
> Having looked at Books Online and elsewhere, I don't see any advice as
> broad based as what I'm seeking.
> Any suggestions would be welcome.
>|||The owner info is of limited use from an audit perspective because the
database owner can be subsequently changed. See my response on security
considerations.
Hope this helps.
Dan Guzman
SQL Server MVP
<MICHAEL_SUNLIN@.COUNTRYWIDE.COM> wrote in message
news:1123249058.756243.222770@.z14g2000cwz.googlegroups.com...
>I think it's mostly an audit trail, to see who created the database.
>|||Thanks for the advice. I liked the idea of a dedicated db owner, to
keep things modular, but the orphaned user concern is a good point.
I'll review those areas mentioned and go with sa as long as the
associated points are not a problem.
Database ownership
Hi
Can an NT GROUP own a database in SQL2000 ? I'm trying to have users in the group create objects with dbo owner ship by default.
However sp_changedbowner does not seem to recogise the group as a login even though sp_helplogins lists it .
Thanksmay be a bug with sp_changedbowner, when done through EM it works.|||Originally posted by Paul Young
may be a bug with sp_changedbowner, when done through EM it works.
Thanks Paul.
I can't seem to see where to do this in EM. Do you mean by adding the group to the dbo role?|||yup!
under Security, right click on the group, select Properties, then Database Access Tab, select the db, and click on the db_owner.|||Originally posted by Paul Young
yup!
under Security, right click on the group, select Properties, then Database Access Tab, select the db, and click on the db_owner.
Thanks again
However I'm getting tables owned by individual nt users which cannot be seen by other members of that group (i'm testing with users
connecting via query analyzer using nt authentication.
What I'm looking for is to be able to get the database owned by
the nt group so that any table created by the users are owned by dbo
and not the individual users, or at least have it so that all users of the group can see and manipulate the tables.
Any ideas?|||user tables are always use instead of dbo tables. Also, don't quote me on this, I think if you are aliased to dbo and alter a uesr table the table remains under the users'd id, it's only when you drop and create that the user identity changes.
Can you move all the user objects to a holding db and try your tests again?|||Originally posted by Paul Young
user tables are always use instead of dbo tables. Also, don't quote me on this, I think if you are aliased to dbo and alter a uesr table the table remains under the users'd id, it's only when you drop and create that the user identity changes.
Can you move all the user objects to a holding db and try your tests again?
Thanks for your patience. When a sql login owns a database then
all tables created are dbo.table rather than sqllogin.table
This is what I'm trying to achieve with the NT group .
When I create a table under an nt user which is in that nt group (whose sql login has dbo access to the database) the table is created as ntuser.table , and that nt user appears as a user in the database (where it didn't appear before ).
I tried to run the sp_addalias to get around this too but this also gave me an error as if it didn't recognise the nt group sql login.
I think I'm on the right track its just annoying that I can't find any real information about this scenario. My other option is to ensure that all tables are explicitly created with dbo. but not all 3rd party applications are so forgiving .
Thanks again for your help Paul
Can an NT GROUP own a database in SQL2000 ? I'm trying to have users in the group create objects with dbo owner ship by default.
However sp_changedbowner does not seem to recogise the group as a login even though sp_helplogins lists it .
Thanksmay be a bug with sp_changedbowner, when done through EM it works.|||Originally posted by Paul Young
may be a bug with sp_changedbowner, when done through EM it works.
Thanks Paul.
I can't seem to see where to do this in EM. Do you mean by adding the group to the dbo role?|||yup!
under Security, right click on the group, select Properties, then Database Access Tab, select the db, and click on the db_owner.|||Originally posted by Paul Young
yup!
under Security, right click on the group, select Properties, then Database Access Tab, select the db, and click on the db_owner.
Thanks again
However I'm getting tables owned by individual nt users which cannot be seen by other members of that group (i'm testing with users
connecting via query analyzer using nt authentication.
What I'm looking for is to be able to get the database owned by
the nt group so that any table created by the users are owned by dbo
and not the individual users, or at least have it so that all users of the group can see and manipulate the tables.
Any ideas?|||user tables are always use instead of dbo tables. Also, don't quote me on this, I think if you are aliased to dbo and alter a uesr table the table remains under the users'd id, it's only when you drop and create that the user identity changes.
Can you move all the user objects to a holding db and try your tests again?|||Originally posted by Paul Young
user tables are always use instead of dbo tables. Also, don't quote me on this, I think if you are aliased to dbo and alter a uesr table the table remains under the users'd id, it's only when you drop and create that the user identity changes.
Can you move all the user objects to a holding db and try your tests again?
Thanks for your patience. When a sql login owns a database then
all tables created are dbo.table rather than sqllogin.table
This is what I'm trying to achieve with the NT group .
When I create a table under an nt user which is in that nt group (whose sql login has dbo access to the database) the table is created as ntuser.table , and that nt user appears as a user in the database (where it didn't appear before ).
I tried to run the sp_addalias to get around this too but this also gave me an error as if it didn't recognise the nt group sql login.
I think I'm on the right track its just annoying that I can't find any real information about this scenario. My other option is to ensure that all tables are explicitly created with dbo. but not all 3rd party applications are so forgiving .
Thanks again for your help Paul
database ownership
I have a database which has stored procedures which have an owner by the name
of essbase. How do I change the ownership of these stored procedures to dbo
Thanks
Check out sp_changeobjectowner.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Raks" <Raks@.discussions.microsoft.com> wrote in message
news:553BF42A-9184-4624-A62F-A730605CDDD0@.microsoft.com...
> I have a database which has stored procedures which have an owner by the name
> of essbase. How do I change the ownership of these stored procedures to dbo
> Thanks
|||Read up on sp_changeobjectowner within Books Online.
Keith
"Raks" <Raks@.discussions.microsoft.com> wrote in message
news:553BF42A-9184-4624-A62F-A730605CDDD0@.microsoft.com...
> I have a database which has stored procedures which have an owner by the
name
> of essbase. How do I change the ownership of these stored procedures to
dbo
> Thanks
|||Be careful, although it is a best practice that dbo own everything, you may
have an application that refers to this sp by its, 2-part name... If so,
then that application will also have to be changed...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Raks" <Raks@.discussions.microsoft.com> wrote in message
news:553BF42A-9184-4624-A62F-A730605CDDD0@.microsoft.com...
> I have a database which has stored procedures which have an owner by the
name
> of essbase. How do I change the ownership of these stored procedures to
dbo
> Thanks
of essbase. How do I change the ownership of these stored procedures to dbo
Thanks
Check out sp_changeobjectowner.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Raks" <Raks@.discussions.microsoft.com> wrote in message
news:553BF42A-9184-4624-A62F-A730605CDDD0@.microsoft.com...
> I have a database which has stored procedures which have an owner by the name
> of essbase. How do I change the ownership of these stored procedures to dbo
> Thanks
|||Read up on sp_changeobjectowner within Books Online.
Keith
"Raks" <Raks@.discussions.microsoft.com> wrote in message
news:553BF42A-9184-4624-A62F-A730605CDDD0@.microsoft.com...
> I have a database which has stored procedures which have an owner by the
name
> of essbase. How do I change the ownership of these stored procedures to
dbo
> Thanks
|||Be careful, although it is a best practice that dbo own everything, you may
have an application that refers to this sp by its, 2-part name... If so,
then that application will also have to be changed...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Raks" <Raks@.discussions.microsoft.com> wrote in message
news:553BF42A-9184-4624-A62F-A730605CDDD0@.microsoft.com...
> I have a database which has stored procedures which have an owner by the
name
> of essbase. How do I change the ownership of these stored procedures to
dbo
> Thanks
database ownership
I have a database which has stored procedures which have an owner by the nam
e
of essbase. How do I change the ownership of these stored procedures to dbo
ThanksCheck out sp_changeobjectowner.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Raks" <Raks@.discussions.microsoft.com> wrote in message
news:553BF42A-9184-4624-A62F-A730605CDDD0@.microsoft.com...
> I have a database which has stored procedures which have an owner by the n
ame
> of essbase. How do I change the ownership of these stored procedures to d
bo
> Thanks|||Read up on sp_changeobjectowner within Books Online.
Keith
"Raks" <Raks@.discussions.microsoft.com> wrote in message
news:553BF42A-9184-4624-A62F-A730605CDDD0@.microsoft.com...
> I have a database which has stored procedures which have an owner by the
name
> of essbase. How do I change the ownership of these stored procedures to
dbo
> Thanks|||Be careful, although it is a best practice that dbo own everything, you may
have an application that refers to this sp by its, 2-part name... If so,
then that application will also have to be changed...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Raks" <Raks@.discussions.microsoft.com> wrote in message
news:553BF42A-9184-4624-A62F-A730605CDDD0@.microsoft.com...
> I have a database which has stored procedures which have an owner by the
name
> of essbase. How do I change the ownership of these stored procedures to
dbo
> Thanks
e
of essbase. How do I change the ownership of these stored procedures to dbo
ThanksCheck out sp_changeobjectowner.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Raks" <Raks@.discussions.microsoft.com> wrote in message
news:553BF42A-9184-4624-A62F-A730605CDDD0@.microsoft.com...
> I have a database which has stored procedures which have an owner by the n
ame
> of essbase. How do I change the ownership of these stored procedures to d
bo
> Thanks|||Read up on sp_changeobjectowner within Books Online.
Keith
"Raks" <Raks@.discussions.microsoft.com> wrote in message
news:553BF42A-9184-4624-A62F-A730605CDDD0@.microsoft.com...
> I have a database which has stored procedures which have an owner by the
name
> of essbase. How do I change the ownership of these stored procedures to
dbo
> Thanks|||Be careful, although it is a best practice that dbo own everything, you may
have an application that refers to this sp by its, 2-part name... If so,
then that application will also have to be changed...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Raks" <Raks@.discussions.microsoft.com> wrote in message
news:553BF42A-9184-4624-A62F-A730605CDDD0@.microsoft.com...
> I have a database which has stored procedures which have an owner by the
name
> of essbase. How do I change the ownership of these stored procedures to
dbo
> Thanks
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 sh
ould make it right now. I
am hoping if you could share some thought and have your comments on the foll
owing live scenario.
1. I have 3 databases(say A,B and C) and they are required together to serv
e 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 d
atabase, but they are allow
ed to pull data from the other two databases. For example, 'a' could read/w
rite 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 an
d C by declaring all of them(a,b,c) to be the owner (dbo) of all three datab
ases, my first question is,
can I declare database ownership on more than one users, or say, can one dat
abase or its objects be owned by more than one login?
Second, from a performance standpoint, there will be no 'broken link' if I a
m 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 dat
abase, 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 (s
uch as stored procedure exe
c., 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 he
r is all I am facing in my shop. I hope you could pardon me by allowing me
to continue bring up the fo
llowing 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 a
pplication, the BigBoy. If
I want to fine tuning the read and write permissions on databases without re
lying on the fronend applications, am I correct to remove the dbowner role f
rom each of the login of the a,b,c user, and use/click the select, update,ex
ec, etc. on the object lis
t from the permission screen for each user?
2. May I assume that the three users(a,b,c) I refer to above, can be replac
ed 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 m
y shop has Network administration personnel and I could separate security ta
sks between Network Admin and DB Admin by using user defined DB Role.
Thank you for looking into this matter.
Martin> 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?
A database may be owned by only one login.
am correct, and may I assume the response time will be better to users?
An unbroken ownership chain eliminates extra security checking but I don't
believe the performance difference is noticeable for most applications. A
significant benefit of an unbroken ownership chain is that permissions on
referenced objects are not needed. This allows you can restrict access to
data through views and procedures. In a multi-database environment like
yours, you could create views referencing tables in the other databases and
then grant select permissions on the views.
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?
You cannot deny permissions from the database owner. The database owner has
full permissions on all objects within the database.
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:
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?
The dbo user and the db_owner role have powerful permissions that are not
normally needed for application access. A best practice is to grant needed
object permissions to roles so that you can control security through user
role membership. This provides more control over permissions.
replaced by or applied to Window's user defined group?
Yes.
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.
Database roles and database users are specific to a particular database but
this really isn't a big deal since you can setup role permissions once and
then control access through role membership. Cross-database chaining
enables you to implement referencing views so that you don't need to create
roles in the other databases. However the logins still need access to the
other databases, either directly or via the guest user security context.
The scripts below illustrates how you can set this up.
-- setup role security with cross-database chaining
USE A
EXEC sp_changedbowner 'MyLogin'
EXEC sp_dboption 'A', 'db chaining', true
EXEC sp_addRole 'ApplicationA'
GRANT ALL ON MyTable TO ApplicationA
GRANT ALL ON MyProc TO ApplicationA
GRANT SELECT ON MyDatabaseB_MyTable_View TO ApplicationA
GRANT SELECT ON MyDatabaseC_MyTable_View TO ApplicationA
GO
USE B
EXEC sp_changedbowner 'MyLogin'
EXEC sp_dboption 'B', 'db chaining', true
EXEC sp_addRole 'ApplicationB'
GRANT ALL ON MyTable TO ApplicationB
GRANT ALL ON MyProc TO ApplicationB
GRANT SELECT ON MyDatabaseA_MyTable_View TO ApplicationB
GRANT SELECT ON MyDatabaseC_MyTable_View TO ApplicationB
GO
USE C
EXEC sp_changedbowner 'MyLogin'
EXEC sp_dboption 'C', 'db chaining', true
EXEC sp_addRole 'ApplicationC'
GRANT ALL ON MyTable TO ApplicationC
GRANT ALL ON MyProc TO ApplicationC
GRANT SELECT ON MyDatabaseA_MyTable_View TO ApplicationC
GRANT SELECT ON MyDatabaseB_MyTable_View TO ApplicationC
GO
-- user setup with cross-database chaining without guest user
EXEC A..sp_grantdbaccess 'MyDomain\UserA'
EXEC A..sp_grantdbaccess 'MyDomain\UserB'
EXEC A..sp_grantdbaccess 'MyDomain\UserC'
EXEC A..sp_addrolemember 'ApplicationA', 'MyDomain\UserA'
EXEC B..sp_grantdbaccess 'MyDomain\UserA'
EXEC B..sp_grantdbaccess 'MyDomain\UserB'
EXEC B..sp_grantdbaccess 'MyDomain\UserC'
EXEC B..sp_addrolemember 'ApplicationB', 'MyDomain\UserB'
EXEC C..sp_grantdbaccess 'MyDomain\UserA'
EXEC C..sp_grantdbaccess 'MyDomain\UserB'
EXEC C..sp_grantdbaccess 'MyDomain\UserC'
EXEC C..sp_addrolemember 'ApplicationC', 'MyDomain\UserC'
GO
-- user setup with cross-database chaining and guest user in each database
GO
EXEC A..sp_grantdbaccess 'MyDomain\UserA'
EXEC A..sp_addrolemember 'ApplicationA', 'MyDomain\UserA'
EXEC B..sp_grantdbaccess 'MyDomain\UserB'
EXEC B..sp_addrolemember 'ApplicationB', 'MyDomain\UserB'
EXEC C..sp_grantdbaccess 'MyDomain\UserC'
EXEC C..sp_addrolemember 'ApplicationC', 'MyDomain\UserC'
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Martin" <anonymous@.discussions.microsoft.com> wrote in message
news:2E80551C-9C11-43E6-9457-98EA275B4A2E@.microsoft.com...
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.
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.
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?
am correct, and may I assume the response time will be better to users?
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?
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:
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?
replaced by or applied to Window's user defined group?
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.
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 sh
ould make it right now. I
am hoping if you could share some thought and have your comments on the foll
owing live scenario.
1. I have 3 databases(say A,B and C) and they are required together to serv
e 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 d
atabase, but they are allow
ed to pull data from the other two databases. For example, 'a' could read/w
rite 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 an
d C by declaring all of them(a,b,c) to be the owner (dbo) of all three datab
ases, my first question is,
can I declare database ownership on more than one users, or say, can one dat
abase or its objects be owned by more than one login?
Second, from a performance standpoint, there will be no 'broken link' if I a
m 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 dat
abase, 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 (s
uch as stored procedure exe
c., 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 he
r is all I am facing in my shop. I hope you could pardon me by allowing me
to continue bring up the fo
llowing 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 a
pplication, the BigBoy. If
I want to fine tuning the read and write permissions on databases without re
lying on the fronend applications, am I correct to remove the dbowner role f
rom each of the login of the a,b,c user, and use/click the select, update,ex
ec, etc. on the object lis
t from the permission screen for each user?
2. May I assume that the three users(a,b,c) I refer to above, can be replac
ed 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 m
y shop has Network administration personnel and I could separate security ta
sks between Network Admin and DB Admin by using user defined DB Role.
Thank you for looking into this matter.
Martin> 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?
A database may be owned by only one login.
quote:
> 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?
An unbroken ownership chain eliminates extra security checking but I don't
believe the performance difference is noticeable for most applications. A
significant benefit of an unbroken ownership chain is that permissions on
referenced objects are not needed. This allows you can restrict access to
data through views and procedures. In a multi-database environment like
yours, you could create views referencing tables in the other databases and
then grant select permissions on the views.
quote:
> 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?
You cannot deny permissions from the database owner. The database owner has
full permissions on all objects within the database.
quote:
> 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:
quote:
> 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?
The dbo user and the db_owner role have powerful permissions that are not
normally needed for application access. A best practice is to grant needed
object permissions to roles so that you can control security through user
role membership. This provides more control over permissions.
quote:
> 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?
Yes.
quote:
> 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.
Database roles and database users are specific to a particular database but
this really isn't a big deal since you can setup role permissions once and
then control access through role membership. Cross-database chaining
enables you to implement referencing views so that you don't need to create
roles in the other databases. However the logins still need access to the
other databases, either directly or via the guest user security context.
The scripts below illustrates how you can set this up.
-- setup role security with cross-database chaining
USE A
EXEC sp_changedbowner 'MyLogin'
EXEC sp_dboption 'A', 'db chaining', true
EXEC sp_addRole 'ApplicationA'
GRANT ALL ON MyTable TO ApplicationA
GRANT ALL ON MyProc TO ApplicationA
GRANT SELECT ON MyDatabaseB_MyTable_View TO ApplicationA
GRANT SELECT ON MyDatabaseC_MyTable_View TO ApplicationA
GO
USE B
EXEC sp_changedbowner 'MyLogin'
EXEC sp_dboption 'B', 'db chaining', true
EXEC sp_addRole 'ApplicationB'
GRANT ALL ON MyTable TO ApplicationB
GRANT ALL ON MyProc TO ApplicationB
GRANT SELECT ON MyDatabaseA_MyTable_View TO ApplicationB
GRANT SELECT ON MyDatabaseC_MyTable_View TO ApplicationB
GO
USE C
EXEC sp_changedbowner 'MyLogin'
EXEC sp_dboption 'C', 'db chaining', true
EXEC sp_addRole 'ApplicationC'
GRANT ALL ON MyTable TO ApplicationC
GRANT ALL ON MyProc TO ApplicationC
GRANT SELECT ON MyDatabaseA_MyTable_View TO ApplicationC
GRANT SELECT ON MyDatabaseB_MyTable_View TO ApplicationC
GO
-- user setup with cross-database chaining without guest user
EXEC A..sp_grantdbaccess 'MyDomain\UserA'
EXEC A..sp_grantdbaccess 'MyDomain\UserB'
EXEC A..sp_grantdbaccess 'MyDomain\UserC'
EXEC A..sp_addrolemember 'ApplicationA', 'MyDomain\UserA'
EXEC B..sp_grantdbaccess 'MyDomain\UserA'
EXEC B..sp_grantdbaccess 'MyDomain\UserB'
EXEC B..sp_grantdbaccess 'MyDomain\UserC'
EXEC B..sp_addrolemember 'ApplicationB', 'MyDomain\UserB'
EXEC C..sp_grantdbaccess 'MyDomain\UserA'
EXEC C..sp_grantdbaccess 'MyDomain\UserB'
EXEC C..sp_grantdbaccess 'MyDomain\UserC'
EXEC C..sp_addrolemember 'ApplicationC', 'MyDomain\UserC'
GO
-- user setup with cross-database chaining and guest user in each database
GO
EXEC A..sp_grantdbaccess 'MyDomain\UserA'
EXEC A..sp_addrolemember 'ApplicationA', 'MyDomain\UserA'
EXEC B..sp_grantdbaccess 'MyDomain\UserB'
EXEC B..sp_addrolemember 'ApplicationB', 'MyDomain\UserB'
EXEC C..sp_grantdbaccess 'MyDomain\UserC'
EXEC C..sp_addrolemember 'ApplicationC', 'MyDomain\UserC'
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Martin" <anonymous@.discussions.microsoft.com> wrote in message
news:2E80551C-9C11-43E6-9457-98EA275B4A2E@.microsoft.com...
quote:
> 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.
quote:
> 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.
quote:
> 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?
quote:
> 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?
quote:
> 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?
quote:
> 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:
quote:
> 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?
quote:
> 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?
quote:
> 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.
quote:
> Thank you for looking into this matter.
> Martin
>
database ownership
I have a database which has stored procedures which have an owner by the name
of essbase. How do I change the ownership of these stored procedures to dbo
ThanksCheck out sp_changeobjectowner.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Raks" <Raks@.discussions.microsoft.com> wrote in message
news:553BF42A-9184-4624-A62F-A730605CDDD0@.microsoft.com...
> I have a database which has stored procedures which have an owner by the name
> of essbase. How do I change the ownership of these stored procedures to dbo
> Thanks|||Read up on sp_changeobjectowner within Books Online.
--
Keith
"Raks" <Raks@.discussions.microsoft.com> wrote in message
news:553BF42A-9184-4624-A62F-A730605CDDD0@.microsoft.com...
> I have a database which has stored procedures which have an owner by the
name
> of essbase. How do I change the ownership of these stored procedures to
dbo
> Thanks|||Be careful, although it is a best practice that dbo own everything, you may
have an application that refers to this sp by its, 2-part name... If so,
then that application will also have to be changed...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Raks" <Raks@.discussions.microsoft.com> wrote in message
news:553BF42A-9184-4624-A62F-A730605CDDD0@.microsoft.com...
> I have a database which has stored procedures which have an owner by the
name
> of essbase. How do I change the ownership of these stored procedures to
dbo
> Thanks
of essbase. How do I change the ownership of these stored procedures to dbo
ThanksCheck out sp_changeobjectowner.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Raks" <Raks@.discussions.microsoft.com> wrote in message
news:553BF42A-9184-4624-A62F-A730605CDDD0@.microsoft.com...
> I have a database which has stored procedures which have an owner by the name
> of essbase. How do I change the ownership of these stored procedures to dbo
> Thanks|||Read up on sp_changeobjectowner within Books Online.
--
Keith
"Raks" <Raks@.discussions.microsoft.com> wrote in message
news:553BF42A-9184-4624-A62F-A730605CDDD0@.microsoft.com...
> I have a database which has stored procedures which have an owner by the
name
> of essbase. How do I change the ownership of these stored procedures to
dbo
> Thanks|||Be careful, although it is a best practice that dbo own everything, you may
have an application that refers to this sp by its, 2-part name... If so,
then that application will also have to be changed...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Raks" <Raks@.discussions.microsoft.com> wrote in message
news:553BF42A-9184-4624-A62F-A730605CDDD0@.microsoft.com...
> I have a database which has stored procedures which have an owner by the
name
> of essbase. How do I change the ownership of these stored procedures to
dbo
> Thanks
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
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
Subscribe to:
Posts (Atom)