Re: Cross database update issue

From: Dan Guzman (danguzman_at_nospam-earthlink.net)
Date: 07/26/03 

Date: Fri, 25 Jul 2003 19:57:23 -0500

To update the table without update permissions, the ownership chain must
be unbroken and cross-database chaining must be enabled (introduced in
SQL 2000 SP3). The owner of the stored procedure and table must both
map to the same login in order for the ownership chain to be unbroken.
Also, for dbo-owned objects, the owner of both databases must be the
same so that the dbo users map to a common login.

You can execute sp_helpdb to determine the current database owners. If
the owners are different, execute sp_changedbowner to change database
ownership.

You can enable cross-database chaining in each of the databases involved
with sp_dboption 'db chaining' or with Enterprise Manager. 

-- 
Hope this helps.
Dan Guzman
SQL Server MVP
-----------------------
SQL FAQ links (courtesy  Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
-----------------------
"Viral Shah" <Viral.shah@intel.com> wrote in message
news:207501c3530d$a9cc3ac0$a001280a@phx.gbl...
> From a store procedure, I am trying to update database
> table in other database.
>
> Here is how it's setup
>
> Database Name: Test
> ======================
> User Name: Test1 Role Name=Test
> Role Test has select permission to table t_test
>
> Database name: Test1
> ========================
> User Name: Test1 Role Name=Test
> User Name: Test1 Role Name=Test
> Role test has execute permission for sp_test
>
> store procedure code:
> create procedure sp_test
> as
> update test..test
> set ctime=getdate()
>
>
> When I execute sp_test in Test1 db I am getting update
> permission denined
> UPDATE permission denied on object 'test',
> database 'test', owner 'dbo'.
>
> This code used to work before..Can you help.

Relevant Pages

  • Re: PHP Query Issues on Ingres R3 Linux Version
    ... If a table is owned by the DBA (the database owner) then there is no ... "permission" thing so much as an object ownership issue. ... An object which is not prefixed by a schema name is found by looking ...
    (comp.databases.ingres)
  • Cross database update issue
    ... Database Name: Test ... User Name: Test1 Role Name=Test ... Role Test has select permission to table t_test ...
    (microsoft.public.sqlserver.security)
  • Re: SP accessing other db
    ... And its dbo user was blank even db Properties shows BUILTIN\Administrator as ... Now the database name is hard-coded. ... > with System Admin permission. ... >> have the same owner so that the dbo users map to the same login. ...
    (microsoft.public.sqlserver.security)
  • Re: database split before or after security?
    ... I created a new database in Access 2003. ... As owner, I created a new database and imported ... database, as workgroup owner and imported queries, forms and reports, ... ModifyDesign permission on no tables. ...
    (microsoft.public.access.security)
  • Re: database split before or after security?
    ... I created a new database in Access 2003. ... As owner, I created a new database and imported ... database, as workgroup owner and imported queries, forms and reports, ... ModifyDesign permission on no tables. ...
    (microsoft.public.access.security)