FIX: Cross-Database INSERTS May Fail with Error 229

 Last reviewed: April 9, 1997
Article ID: Q160541
The information in this article applies to:
  • Microsoft SQL Server, version 6.5
BUG #: 16276

SYMPTOMS

A stored procedure run by a user who has full execute permissions and who is a valid user in two databases may fail with the following error on a cross-database INSERT: 

   229 %s permission denied on object %.*s, database %.*s, owner %.*s
This problem occurs even if the owner of the underlying table is also the owner of the stored procedure. This problem occurs if the stored procedure needs to be re-resolved, such as in the following cases: loading from backup, dropping and recreating an underlying table, or shutting down and restarting SQL Server (if the stored procedure references a temporary table). If the stored procedure is dropped and re-created, it works properly until one of the re-resolutions mentioned above occurs.

WORKAROUND

To work around this problem, do either of the following:

  • Take the temporary table creation out of the stored procedure; create the temporary table before calling the stored procedure.

    -or-

  • Drop and re-create the affected stored procedure after a re-resolution event has occurred.

STATUS

Microsoft has confirmed this to be a problem in SQL Server version version 6.5. This problem has been corrected in U.S. Service Pack 2 for Microsoft SQL Server version 6.5. For more information, contact your primary support provider.

Additional query words:
Keywords : kbbug6.50 kbfix6.50.sp2 kbusage SSrvStProc
Version : 6.5
Platform : WINDOWS

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: April 9, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.