permissions problem

I have what I'm sure is a simple problem with setting permissions on some
objects. Here's the situation... I have one database (Central) that I want
to allow inserts to a table (Queue) on via a stored procedure. That stored
procedure I am storing in the master database so that any of the other
databases I have running can execute it.
I am doing the insert in the procedure by referencing "INSERT INTO
Central.dbo.Queue..." That procedure is set to allow EXEC permissions by
the guest and public roles.
I have logins from the other databases 'Permit in Database Role' set to
public for the Queue table. The error I am getting is "INSERT permission
denied on object 'Queue', database 'Central', owner 'dbo'.
What am I missing'> What am I missing'
If your stored procedure is owned by 'dbo', then your Central database needs
to also be owned by 'sa' so that the ownership chain is unbroken. Also, if
you are running SQL 2000 SP3+, you'll need to enable cross-database chaining
('db chaining' database option) un the Central database.
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
"Random" <cipherlad@.hotmail.com> wrote in message
news:u2otNyjOFHA.244@.TK2MSFTNGP12.phx.gbl...
>I have what I'm sure is a simple problem with setting permissions on some
>objects. Here's the situation... I have one database (Central) that I want
>to allow inserts to a table (Queue) on via a stored procedure. That stored
>procedure I am storing in the master database so that any of the other
>databases I have running can execute it.
> I am doing the insert in the procedure by referencing "INSERT INTO
> Central.dbo.Queue..." That procedure is set to allow EXEC permissions by
> the guest and public roles.
> I have logins from the other databases 'Permit in Database Role' set to
> public for the Queue table. The error I am getting is "INSERT permission
> denied on object 'Queue', database 'Central', owner 'dbo'.
> What am I missing'
>|||Thank you for replying. The owner on the Central database is 'sa'.
Where is the option for 'db chaining' set? I can't locate it in books
online.
I have also ensured that only symin members can create dbo-owned objects
in the database.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:ecJw5QkOFHA.2708@.tk2msftngp13.phx.gbl...
> If your stored procedure is owned by 'dbo', then your Central database
> needs to also be owned by 'sa' so that the ownership chain is unbroken.
> Also, if you are running SQL 2000 SP3+, you'll need to enable
> cross-database chaining ('db chaining' database option) un the Central
> database.
> 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
> "Random" <cipherlad@.hotmail.com> wrote in message
> news:u2otNyjOFHA.244@.TK2MSFTNGP12.phx.gbl...
>|||The 'db chaining' database option is aka cross-database chaining. One
method to turn it on is with sp_dboption via Query Analyzer:
EXEC sp_dboption 'Central' ,'db chaining', true
Hope this helps.
Dan Guzman
SQL Server MVP
"Random" <cipherlad@.hotmail.com> wrote in message
news:OErPV0rOFHA.3444@.tk2msftngp13.phx.gbl...
> Thank you for replying. The owner on the Central database is 'sa'.
> Where is the option for 'db chaining' set? I can't locate it in books
> online.
> I have also ensured that only symin members can create dbo-owned
> objects in the database.
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:ecJw5QkOFHA.2708@.tk2msftngp13.phx.gbl...
>|||FYI, setting the 'db_chaining' option did the trick. I found the
documentation and read about the security implications, so I've got it
covered now. Thanks for the tips!
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OY7$75vOFHA.2520@.tk2msftngp13.phx.gbl...
> The 'db chaining' database option is aka cross-database chaining. One
> method to turn it on is with sp_dboption via Query Analyzer:
> EXEC sp_dboption 'Central' ,'db chaining', true
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Random" <cipherlad@.hotmail.com> wrote in message
> news:OErPV0rOFHA.3444@.tk2msftngp13.phx.gbl...
>