"Cross database ownership chaining"

I saw this option in SSMS under "Servers, Property, Security tab". I looked at BOL but I still don't understand what it is or what it is used. Any help appreciated.

TIA,

Barkingdog

I think that this is detailed described in the BOL. To summarize that shortly. If one user creates a view or a procedure and grant appropiate permissions for THAT object to some user for Select / Execute and the object accesses objects in other databases which are also owned by the creator of the View/Procedure then with Cross database ownership chain SQL Server assumes that the permissions don′t have to be checked for the accessing user of the view/procedure. if the ownership chain is broken, or the function is disabled permissions are check also for the access to the second database for the accessing user. For this case you would have to grant the user permissions in the second database whereas you would not need to in the first case.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Ownership Chaining

By default, all database objects have owners. When an object such as a view, a stored procedure, or a user-defined function references another object, an ownership chain is established. For example, a table that is owned by the same user. When the same user owns the source object, the view, stored procedure, or user-defined function, and all target objects (underlying tables, views, or other objects), the ownership chain is said to be unbroken. When the ownership chain is unbroken, SQL Server checks permissions on the source object but not on the target objects.

Cross-Database Ownership Chaining

Cross-database ownership chaining occurs when the source object depends on objects in another database. A cross-database ownership chain works in the same way as ownership chaining in a database, except that an unbroken ownership chain is based on all the object owners being mapped to the same login account. Therefore, in a cross-database ownership chain, if the source object in the source database and the target objects in the target databases are owned by the same login account, SQL Server does not check permissions on the target objects.
If you have more than one database used by an application, and that application calls stored procedures or views in a database that is based on objects in another database, then cross-database ownership chaining is used. Applications that rely on cross-database ownership chaining may generate permission denied errors if cross-database ownership chaining option is turned off.

Risks Associated with Cross-Database Ownership Chaining

Microsoft recommends that you disable the cross-database ownership chaining option because of the actions that highly-privileged users can perform:

Database owners and members of the db_ddladmin or the db_owners database roles can create objects that are owned by other users. These objects can potentially target objects in other databases. This means that if you enable cross-database ownership chaining, you must fully trust these users with data in all databases. To identify the members of the db_ddladmin and the db_owners roles in the current database, execute the following Transact-SQL commands: EXEC sp_configure 'Cross DB Ownership Chaining', '1'; RECONFIGURE Configure cross-database ownership chaining at the database level with the new db chaining option for sp_dboption. When this option is set to false, the database cannot participate in cross-database ownership chaining as either the source or the target database. When this option is set to true, the database can participate in a cross-database ownership chain. By default, this option is false for all user databases after you apply SQL Server 2000 SP3. The following command enables cross-database ownership chaining for the Northwind database: EXEC sp_dboption 'Northwind', 'db chaining', 'true' The effects of sp_dboption are manifested only when the sp_configure Cross DB Ownership Chaining option is set to 0. Also, to enable cross-database ownership chaining at the database level, you must enable this option on both the source and the target database. Configuring cross-database ownership chaining by using SQL Enterprise Manager: ? To set this option for all databases, follow these steps: 1. Right-click <server>. 2. Click to select Properties. 3. Click Security. 4. Click to select Allow cross-database ownership chaining in the Ownership chaining section. 5. Click OK. You are prompted to stop and restart the SQL Server services. 6. Click OK . ? To enable this option at the database level, follow these steps: 1. Right-click the <database>. 2. Click to select Properties. 3. Click Options. 4. Click to select Allow Cross Database Ownership Chaining in the Settings section

"Codezone Community" and "Questons" Providers in BOL 2005

Having recently started a new online MSFT focused community, I was curious as to if there will be a future release of BOL 2005 where users can "plugin" custom communities that adhere to some future defined interfaces?

thanks,

derek comingore

Hi Derek,

Just wanted to let you know that I'm checking into this and will repost when I get an answer.

Thanks,

Gail

|||

Hi Derek,

Sorry about the delayed response. I was told that if you really want to make your community's content available to the larger SQL Server customer base (or the entire VS customer base in case of a developer community), then you should apply for membership in the CodeZone program. http://www.codezone.com/MyCodezone.CodezoneCom?ActiveID=2001

Hope that gets you going.

Regards, Gail

"ALTER INDEX <tablename> REBUILD"

I have read through BOL but am still confused by the above sql. I think it rebuilds all indexes on a table. Am I correct? (If so, if would seem to be a good thing to run it nightly on all tables in all databases. Or maybe that is too extreme)

Barkingdog

This will rebuild all indexes on a table

Code Snippet

ALTER INDEX ALL ON YourTable REBUILD.

The following will rebuild an individual index:

Code Snippet

ALTER INDEX YourIndexName ON YourTable REBUILD

This is often a resource intensive process as locks are taken out on the table and should ideally be scheduled for a period of low usage. As for how often you need to do this, i would guess it depends on how fragmented your indexes get. This can be checked by looking in sys.dm_db_index_physical_stats.

HTH!

<Never mind, self-ansered> DMF - is it by design that database can't subscribe to Def

If by design, I can't really understand why we would not allow that...

[EDIT]

Answered this myself... BOL says:

Declarative Management Framework policy group
A user-defined group to help manage policies. Users can classify policies into different policy groups. A policy belongs to one and only one policy group. Database owners can subscribe a database to a set of policy groups. Only policies from its subscribed groups can govern a database. All databases implicitly subscribe to the default policy group.

There's a specific reason for this. The Policy Administrator may decide that all database on the system must adhere to a group of policies. To enable this these policies are placed int he default group. Furthermore, there may a group of policies that only apply to certain databases but the Policy Administrator doesn't "know" which database. Therefore, the Policy Adminsitrator can leave it up to the database owner to select which other groups (other than default) apply to their particular database. The database owner does this by subscribing to these other groups.

Cheers,
Dan

|||

Thanks.

FYI:

I will file a connect bug to request that any database shows subscribttion to Default group (Right-click a database -> Policies -> Group Subscriptions).

Of course, it should be impossible to unsubscribe.

This will visually represent that all databases are implicitly subscribe to Default policy and will remove any potential confusion (like I encountered initially.)

|||

Hi Alexey, We originally showed it and thought it was confusing. Looks like we were wrong...

So yes, please file the bug.

Cheers,

Dan

|||

Hm... If this is also found confusing, 2 middle-ground options:

Do show the default policy and change "Group Subscriptions:" wording of the dialog to "Group Subscriptions, including implicit subscription to Default policy:" Do not show the default policy, and add at the bottom (just above Ok/Cancel):
Note:all databases also implicitly subscribe to Default policy.|||

Hi Alexey,

Let's take this offline.

Thanks,

Dan