INF: Enabling Cross-Database Ownership Chaining for Notification Services Databases (813849)

Microsoft SQL Server 2000 Service Pack 3 (SP3) introduces options for configuring cross-database ownership chaining. When you install SP3, cross-database ownership chaining is disabled for all user databases by default. After installation, you can enable cross-database ownership chaining for individual databases using the sp_dboption system stored procedure.

An instance of Notification Services uses two or more databases: one instance database and one or more application databases. These databases require cross-database ownership chaining to allow Notification Services stored procedures to access objects in the instance and application databases.

MORE INFORMATION

If you accepted the default behavior during SP3 Setup, you must enable cross-database ownership chaining for all Notification Services databases.

Enabling Cross-Database Ownership Chaining for Existing Notification Services Databases

If you have existing Notification Services applications, use the following code to enable cross-database ownership chaining after installing SP3:

USE master

EXEC sp_dboption InstanceDBName, 'db chaining', 'true'

EXEC sp_dboption ApplicationDBName, 'db chaining', 'true'

GO

You must run the sp_dboption stored procedure for each instance and application database.

NOTE: If you detach and then reattach a database, you must re-enable cross-database ownership chaining.

Deploying New Instances of Notification Services

When you deploy a new instance of Notification Services, use the NSControl Create command to create the instance and application databases, and then use the code shown above to turn on cross-database ownership chaining for the instance and application databases.

Members of the dbcreator fixed server role can run the NSControl Create command; however, only members of the sysadmin fixed server role can configure cross-database ownership chaining.

REFERENCES

Additional information about cross-database ownership chaining is available in SQL Server 2000 Books Online (Updated - SP3). The following topics address cross-database ownership chaining:

Backward Compatibility for Cross-Database Ownership Chaining
Using Ownership Chains
sp_dboption
Setting Configuration Options

To download SQL Server 2000 Books Online (Updated - SP3), visit the following Microsoft Web site:
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp