database owner chaining

A question of database owner chaining:
I have a SQL 200 server.
In master the database owner is sa.
In a db called SPOA the database owner is sa. (sp_changedbowner 'sa')
The dbo is linked to login sa.
I create a stored procedure in SPOA:
create proc test_OA as
DECLARE @.object int
DECLARE @.hr int
DECLARE @.src varchar(255), @.desc varchar(255)
EXEC @.hr = sp_OACreate 'SQLDMO.SQLServer', @.object OUT
IF @.hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc OUT
SELECT hr=convert(varbinary(4),@.hr), Source=@.src, Description=@.desc
RETURN
END
I create a user in SPOA
sp_addlogin sune
sp_adduser sune
grant execute on test_OA to sune
I connect to SPOA as sune and do EXECUTE test_OA
As expected I get: EXECUTE permission denied on object 'sp_OACreate',
database 'master'
I then give user sune role db_owner.
I connect again to SPOA as sune and do EXECUTE test_OA
And I get: EXECUTE permission denied on object 'sp_OACreate', database
'master'
I expected it to work.
What have I missed?
/kCross database ownership chaining turned is off by default in SQL 2000 SP3+.
You can selectively turn it on in your user database using sp_dboption:
EXEC sp_dboption 'SPOA', 'db chaining', true
Note that you should enable cross-database chaining in an sa-owned database
when only symin role members can create dbo-owned objects in that
database.
Hope this helps.
Dan Guzman
SQL Server MVP
"kurt sune" <apa@.apa.com> wrote in message
news:elLPghnGFHA.2276@.TK2MSFTNGP15.phx.gbl...
>A question of database owner chaining:
> I have a SQL 200 server.
> In master the database owner is sa.
> In a db called SPOA the database owner is sa. (sp_changedbowner 'sa')
> The dbo is linked to login sa.
> I create a stored procedure in SPOA:
> create proc test_OA as
> DECLARE @.object int
> DECLARE @.hr int
> DECLARE @.src varchar(255), @.desc varchar(255)
> EXEC @.hr = sp_OACreate 'SQLDMO.SQLServer', @.object OUT
> IF @.hr <> 0
> BEGIN
> EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc OUT
> SELECT hr=convert(varbinary(4),@.hr), Source=@.src, Description=@.desc
> RETURN
> END
> I create a user in SPOA
> sp_addlogin sune
> sp_adduser sune
> grant execute on test_OA to sune
> I connect to SPOA as sune and do EXECUTE test_OA
> As expected I get: EXECUTE permission denied on object 'sp_OACreate',
> database 'master'
> I then give user sune role db_owner.
> I connect again to SPOA as sune and do EXECUTE test_OA
> And I get: EXECUTE permission denied on object 'sp_OACreate', database
> 'master'
> I expected it to work.
> What have I missed?
> /k
>|||yes it helped.
THANKS!!!
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:uG1JAunGFHA.2732@.TK2MSFTNGP15.phx.gbl...
> Cross database ownership chaining turned is off by default in SQL 2000
SP3+.
> You can selectively turn it on in your user database using sp_dboption:
> EXEC sp_dboption 'SPOA', 'db chaining', true
> Note that you should enable cross-database chaining in an sa-owned
database
> when only symin role members can create dbo-owned objects in that
> database.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "kurt sune" <apa@.apa.com> wrote in message
> news:elLPghnGFHA.2276@.TK2MSFTNGP15.phx.gbl...
>|||Does anyone know what table I need to look into for SQL 2000 which stores db
o's name and in VSQL, IP address & port number.
Many Thanks