BUG: 229 Error on Stored Procedures Dependent Object

ID: Q156069

The information in this article applies to:

Microsoft SQL Server version 6.5

SYMPTOMS

Error 229 may occur on a stored procedures dependent object (that is, a table, view, or other stored procedure) when running the stored procedure with the following conditions:

Both the stored procedure and the dependent object are owned by the same owner, but not dbo.

Both the stored procedure and the dependent object are created by the dbo with the optional owner.object naming convention.

Permissions are only granted on the stored procedure and not the dependent object.

WORKAROUND

Use the SETUSER command to impersonate the object owner while creating the dependent object and stored procedure.

STATUS

Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.5. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

Here is an example of a create procedure command that would cause this problem if created by the sa login:


   CREATE TABLE objowner.permtest (a int)
   GO
   CREATE PROCEDURE objowner.sp_permtest AS
   SELECT * FROM objowner.permtest
   GO
   GRANT EXECUTE ON objowner.sp_permtest TO probe
   GO

The following error occurs when the stored procedure is run by the probe login:

Msg 229, Level 14, State 1
SELECT permission denied on object permtest, database pubs, owner
objowner

Additional query words: ownership chain access denied stored procedure


Keywords          : kbnetwork SSrvStProc kbbug6.50 
Version           : 6.5
Platform          : WINDOWS 
Issue type        :

Last Reviewed: April 2, 1999