RE: cross database query with ownership chaining

Hi Mark,
I just found a KB article talking about the error you encountered. If this
issue persists, please refer to this KB article to see if you are
encountering the same issue:
You cannot run a statement or a module that includes the EXECUTE AS clause
after you restore a database in SQL Server 2005
http://support.microsoft.com/kb/913423/en-us

As it stated that this problem occurs when all the following conditions are
true:
- You back up a database from an instance of SQL Server 2005. Then, you
restore the database to an instance of SQL Server 2005 that is installed on
another computer.
- The statement or the module is executed as the dbo (database owner) user.
- The owner of the database is a domain user or a SQL Server authorization
login.

Notes:
- If a domain user owns the database, the computer on which you restore the
database cannot access the domain.
- If a SQL Server authorization login owns the database, the login does not
exist in the new instance of SQL Server 2005.

The cause is that this problem occurs because SQL Server 2005 cannot obtain
the information about the context when you try to impersonate a database
user to run a statement or a module.

To work around this problem, change the database owner to a valid login or
domain user. To do this, run the following statements: USE <DatabaseName>
GO
sp_changedbowner '<NewLogin>'

We really appreciate your posting back and let us know the issue status. If
you have any further questions or concerns, please feel free to let us
know. We are very glad to assist you further.


Best regards,
Charles Wang
Microsoft Online Community Support
=========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@xxxxxxxxxxxxxx
=========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================



.

Relevant Pages

  • Idiomatic Expressions to Operating System Architecture: Transforming Rigids
    ... <Subject: Re: Idiomatic Expressions to Operating ... <endpoint during a single session * DATABASE + by ... <statements is a new feature of SQL Server 2005. ...
    (sci.math)
  • Re: System Administrator Implied Permissions
    ... > sa login, it assigns it the System Administrator fixed ... > Now, given this, why does SQL Server ... in each database is always a member of the public and db_owner roles. ... Other sysadmin role members have the exact same ...
    (microsoft.public.sqlserver.security)
  • Re: No db access after publishing web site
    ... GRANT UPDATE TO ... If I detach and attach this database on a different PC (according that PC ... Cannot open database "pago" requested by the login. ... Are you detaching/attaching the SQL Server Express database correctly ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: cannot login to the db after...
    ... Jasper Smith (SQL Server MVP) ... I have created a new database, "db_1", using the "sa" ... I then created a new login, "sqluser1" and gave ...
    (microsoft.public.sqlserver.security)
  • Re: Cant view merge agent properties (trying again)
    ... In the List of Actions for the Snapshot Agent History I see this repeated: ... every single database listed. ... So, just now, I went to computername\Administrator Login ID (because it's ... On the computer running SQL Server, ...
    (microsoft.public.sqlserver.replication)