Re: EXECUTE Permissions and Cross Database

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:
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@xxxxxxxxx> wrote in message
news:1153149830.956575.279290@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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:
Check the database property to allow cross database ownership chaining.



--
Arnie Rowland
"To be successful, your heart must accompany your knowledge."



"Micky McQuade" <javamick@xxxxxxxxx> wrote in message
news:1153144180.968800.32910@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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



.

Relevant Pages

  • Re: Viewing object owner in SQL 2005 - ownership chaining
    ... The server principal "HFDB_2_0_readonly" is not able to access the database "Hotfix_data" under the current security context. ... To add on to Erland's response, the user needs a security context in both databases, even if no permissions are granted. ... I'm trying to set up Ownership Chaining. ... When I set up both a table and view from one logon and granted permissions to the view it didn't work, evidently because "creating user" is not equivalent to OWNER. ...
    (microsoft.public.sqlserver.security)
  • Re: current security context is not trusted (cross db ownership ch
    ... ALTER DATABASE only in the specific cases where needed. ... The instance of SQL Server will recognize this setting when the cross ... db ownership chaining server option is 0. ...
    (microsoft.public.sqlserver.security)
  • Re: current security context is not trusted (cross db ownership ch
    ... The instance of SQL Server will recognize this setting when the cross ... database option is ignored when 'cross db ownership chaining' of sp_configure is set to 1. ...
    (microsoft.public.sqlserver.security)
  • Re: current security context is not trusted (cross db ownership ch
    ... and database level. ... The instance of SQL Server will recognize this setting when the cross ... db ownership chaining server option is 0. ...
    (microsoft.public.sqlserver.security)
  • Re: Trigger**
    ... if table 2 is in another database, you must enable ownership chaining. ... > destination table) ... > table,an error occurred that you have no permission to update the second ...
    (microsoft.public.sqlserver.programming)