Cross-database Ownership Chaining in SQL 2005

Hi,
Sorry to post in this group. It seems only few people in SQL 2005 newsgroup
now.
I met a problem in SQL SERVER 2005 SEPTEMBER CTP. The option "Cross
Database Ownership Chaining" won't work in SQL 2005 Step CTP. But there is
no problem if I call stored procedure to access a table in the sample databa
se--AdventureWorks. for
example:
use msdb
go
drop proc sp_my_test
go
create proc sp_my_test
as
select * from AdventureWorks.Person.Address
--select * from pubs.dbo.test2
go
grant exec on sp_my_test to wdw
go
"wdw" is a normal account with minimal permission. When I called sp_my_test
I got the data return.
But if I try to call a stored procedure to access a table in a new user data
base I got error: for
example:
use msdb
go
drop proc sp_my_test
go
create proc sp_my_test
as
select * from pubs.dbo.test2
go
grant exec on sp_my_test to wdw
go
"wdw" is the same account with minimal permission. Pubs database and test2 t
able are created by sa account.
When I call sp_my_test I met permission denied error:
The error is:
Msg 229, Level 14, State 5, Procedure my_test, Line 4
SELECT permission denied on object 'test2', database 'pubs', schema 'dbo'.
I changed the server and database option for Cross Database Ownership
Chaining. Neither works in my database but it DOES work for AdventureWorks.
Even I
disable this option at server and database level it still works for Adventur
eWorks database. Can you do this simple test on your SQL 2005 and post your
result?
Thanks for any help!
BillIn order for the cross-database chain to be unbroken for schema owned by
'dbo' users, the databases need to be owned by the same login. The msdb
system database is owned by 'sa' so I would expect your test to work if you
change the owner of your 'pubs' database to 'sa' and turn on the DB_CHAINING
database option:
ALTER AUTHORIZATION ON DATABASE::pubs
TO sa
ALTER DATABASE pubs
SET DB_CHAINING ON
Hope this helps.
Dan Guzman
SQL Server MVP
"Bill Wang" <wdw2130833@.hotmail.com> wrote in message
news:%23BO$hZ82FHA.3136@.TK2MSFTNGP09.phx.gbl...
Hi,
Sorry to post in this group. It seems only few people in SQL 2005 newsgroup
now.
I met a problem in SQL SERVER 2005 SEPTEMBER CTP. The option "Cross
Database Ownership Chaining" won't work in SQL 2005 Step CTP. But there is
no problem if I call stored procedure to access a table in the sample
database--AdventureWorks. for
example:
use msdb
go
drop proc sp_my_test
go
create proc sp_my_test
as
select * from AdventureWorks.Person.Address
--select * from pubs.dbo.test2
go
grant exec on sp_my_test to wdw
go
"wdw" is a normal account with minimal permission. When I called sp_my_test
I got the data return.
But if I try to call a stored procedure to access a table in a new user
database I got error: for
example:
use msdb
go
drop proc sp_my_test
go
create proc sp_my_test
as
select * from pubs.dbo.test2
go
grant exec on sp_my_test to wdw
go
"wdw" is the same account with minimal permission. Pubs database and test2
table are created by sa account.
When I call sp_my_test I met permission denied error:
The error is:
Msg 229, Level 14, State 5, Procedure my_test, Line 4
SELECT permission denied on object 'test2', database 'pubs', schema 'dbo'.
I changed the server and database option for Cross Database Ownership
Chaining. Neither works in my database but it DOES work for AdventureWorks.
Even I
disable this option at server and database level it still works for
AdventureWorks database. Can you do this simple test on your SQL 2005 and
post your result?
Thanks for any help!
Bill