BUG: 229 Error on Stored Procedures Dependent Object (156069)

The information in this article applies to:

Microsoft SQL Server 6.5

This article was previously published under Q156069

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

Modification Type:	Major	Last Reviewed:	10/3/2003
Keywords:	kbBug kbnetwork kbnofix KB156069