 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / General / Security / July 2006EXECUTE 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, Micky Verify that the DB optoin to allow 'Cross DB Ownership Chaining' is set on for all databases involved.  SignatureArnie Rowland "To be successful, your heart must accompany your knowledge." >I have an odd situation. Here are the details: > [quoted text clipped - 14 lines] > Thanks, > Micky Yes, that is set at the server level to allow. Micky > Verify that the DB optoin to allow 'Cross DB Ownership Chaining' is set on > for all databases involved. [quoted text clipped - 21 lines] > > Thanks, > > Micky Check the database property to allow cross database ownership chaining. SignatureArnie Rowland "To be successful, your heart must accompany your knowledge." >I have an odd situation. Here are the details: > [quoted text clipped - 14 lines] > 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 > Check the database property to allow cross database ownership chaining. > [quoted text clipped - 20 lines] > > Thanks, > > Micky It should be set to true. It can be set on the individual database level. SignatureArnie Rowland "To be successful, your heart must accompany your knowledge." > 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. [quoted text clipped - 27 lines] >> > Thanks, >> > Micky 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 > It should be set to true. It can be set on the individual database level. > [quoted text clipped - 33 lines] > >> > Thanks, > >> > Micky >I have an odd situation. Here are the details: > [quoted text clipped - 11 lines] > > 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 > >I have an odd situation. Here are the details: > > [quoted text clipped - 16 lines] > > David 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 > Yes, the login is the same in both cases. > [quoted text clipped - 21 lines] >> >> David Just for the info of others that may have the same situation....resetting the db owner seemed to have taken care of the problem. (even though the owner was already sa). Using sp_changedbowner Thanks, Micky > Hi > Make sure that the user on C database IS an owner of 'dbo' SCHEMA as well [quoted text clipped - 25 lines] > >> > >> David |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.