SQL Server 2000 SP3 1

[moonshadow]

I'm keen to upgrade our companies servers from SQL Server 2000 SP2 to SP3, as a precaution against the Slammer viruses. However, our technical expert says:
"SP3 attempts to modify the cross-database ownership chains. Until we can be sure what this actually means to us (by testing) I do not believe it advisable to try it."
I've searched on the internet, and can't find any reference to this. Anyone got any ideas?

 

[gavinhuet]

The easiest way to answer this is by example.

Suppose you have 2 databases on a server - DB1 and DB2.
You apply security to your databases, something like no access to tables, view only access to data through views, modifications to data only through stored procedures.

You have a stored procedure that resides in DB1 that updates a table in DB2 :
UPDATE DB2.dbo.MyTable SET MyValue='x'

Under SP2 this works fine.
Under SP3 with default chaining this does not work.

SP3 looks at your permissions, though you have permission to execute the stored procedure, because the table exists in another database and you do not have permission to update that table directly, you are denied permission to do the update.
If you then change your permissiosn to allow direct modification of the table in DB2 your stored procedure will work, or you can change the default behaviour for chaining to work as per SP2. This can be changed under the Server Properties, Security tab.

I hope this helps.

 

[moonshadow]

What a clear and helpful explanation. Many thanks. I can see now that it's just an excuse to do nothing.

 

[JamesLean]

I would emphasise the point that you can specify not to change the chaining behaviour, both during setup and after installation and at the instance or database level.

This means you could easily install the SP, and get all the other benefits including slammer protection, without affecting the ownership chaining and just switch on that functionality (should you even need to) after you have tested successfully.

--James