EXECUTE Permissions and Cross Database

I have an odd situation. Here are the details:
- I have three databases (A, B, C).
- I have a user that has EXECUTE and SELECT permissions on each
database.
- I have a stored procedure in A and B that does an update in C at one
point
- The stored procedure works fine from database A, but from database B,
it gives me the following UPDATE error: UPDATE permission denied on
object 'MyTable', database 'C', schema 'dbo'
- No dynamic SQL is used
- The database owners are the same as well as the table and stored
procedure owners
Can anyone help guide me on this?
Thanks,
MickyVerify that the DB optoin to allow 'Cross DB Ownership Chaining' is set on
for all databases involved.
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"Micky McQuade" <javamick@.gmail.com> wrote in message
news:1153144180.968800.32910@.35g2000cwc.googlegroups.com...
>I have an odd situation. Here are the details:
> - I have three databases (A, B, C).
> - I have a user that has EXECUTE and SELECT permissions on each
> database.
> - I have a stored procedure in A and B that does an update in C at one
> point
> - The stored procedure works fine from database A, but from database B,
> it gives me the following UPDATE error: UPDATE permission denied on
> object 'MyTable', database 'C', schema 'dbo'
> - No dynamic SQL is used
> - The database owners are the same as well as the table and stored
> procedure owners
> Can anyone help guide me on this?
> Thanks,
> Micky
>|||Yes, that is set at the server level to allow.
Micky
Arnie Rowland wrote:[vbcol=seagreen]
> Verify that the DB optoin to allow 'Cross DB Ownership Chaining' is set on
> for all databases involved.
> --
> Arnie Rowland
> "To be successful, your heart must accompany your knowledge."
>
> "Micky McQuade" <javamick@.gmail.com> wrote in message
> news:1153144180.968800.32910@.35g2000cwc.googlegroups.com...|||Check the database property to allow cross database ownership chaining.
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"Micky McQuade" <javamick@.gmail.com> wrote in message
news:1153144180.968800.32910@.35g2000cwc.googlegroups.com...
>I have an odd situation. Here are the details:
> - I have three databases (A, B, C).
> - I have a user that has EXECUTE and SELECT permissions on each
> database.
> - I have a stored procedure in A and B that does an update in C at one
> point
> - The stored procedure works fine from database A, but from database B,
> it gives me the following UPDATE error: UPDATE permission denied on
> object 'MyTable', database 'C', schema 'dbo'
> - No dynamic SQL is used
> - The database owners are the same as well as the table and stored
> procedure owners
> Can anyone help guide me on this?
> Thanks,
> Micky
>|||It is set to false, but it is greyed out because of the server setting
I assume. They are set to compatability level 80 if that matters.
Also, they are all set to the same thing (chaining wise) which is what
stumps me (it works from Database A but not B)
Micky
Arnie Rowland wrote:[vbcol=seagreen]
> Check the database property to allow cross database ownership chaining.
>
> --
> Arnie Rowland
> "To be successful, your heart must accompany your knowledge."
>
> "Micky McQuade" <javamick@.gmail.com> wrote in message
> news:1153144180.968800.32910@.35g2000cwc.googlegroups.com...|||It should be set to true. It can be set on the individual database level.
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"MickyM" <javamick@.gmail.com> wrote in message
news:1153149830.956575.279290@.p79g2000cwp.googlegroups.com...
> It is set to false, but it is greyed out because of the server setting
> I assume. They are set to compatability level 80 if that matters.
> Also, they are all set to the same thing (chaining wise) which is what
> stumps me (it works from Database A but not B)
> Micky
> Arnie Rowland wrote:
>|||"Micky McQuade" <javamick@.gmail.com> wrote in message
news:1153144180.968800.32910@.35g2000cwc.googlegroups.com...
>I have an odd situation. Here are the details:
> - I have three databases (A, B, C).
> - I have a user that has EXECUTE and SELECT permissions on each
> database.
> - I have a stored procedure in A and B that does an update in C at one
> point
> - The stored procedure works fine from database A, but from database B,
> it gives me the following UPDATE error: UPDATE permission denied on
> object 'MyTable', database 'C', schema 'dbo'
> - No dynamic SQL is used
> - The database owners are the same as well as the table and stored
> procedure owners
> Can anyone help guide me on this?
>
Is the login executing the procedure the same in both cases? The login must
have access to database C.
David|||Yes, the login is the same in both cases.
Micky
David Browne wrote:
> "Micky McQuade" <javamick@.gmail.com> wrote in message
> news:1153144180.968800.32910@.35g2000cwc.googlegroups.com...
>
> Is the login executing the procedure the same in both cases? The login mu
st
> have access to database C.
> David|||ok, this is resolved now. The bad part is I still don't have a clear
understanding of why the error was happening. I know it was related to
ownership chaining, but I don't know why. Here is what I did. I ran
this:
EXEC sys.sp_configure N'cross db ownership chaining', N'0'
GO
RECONFIGURE WITH OVERRIDE
GO
and then
EXEC sys.sp_configure N'cross db ownership chaining', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO
So, basically I turned it off and then back on (at the server level).
Thanks for the help on this.
Micky
Arnie Rowland wrote:[vbcol=seagreen]
> It should be set to true. It can be set on the individual database level.
> --
> Arnie Rowland
> "To be successful, your heart must accompany your knowledge."
>
> "MickyM" <javamick@.gmail.com> wrote in message
> news:1153149830.956575.279290@.p79g2000cwp.googlegroups.com...|||Hi
Make sure that the user on C database IS an owner of 'dbo' SCHEMA as well
. You cann add them and grant any permissions that you want
"MickyM" <javamick@.gmail.com> wrote in message
news:1153160895.236692.9320@.75g2000cwc.googlegroups.com...
> Yes, the login is the same in both cases.
> Micky
> David Browne wrote:
>