BUG: Grant or Revoke Statements Fail When Used in an SP

• Microsoft SQL Server version 6.0

SYMPTOMS

A GRANT or REVOKE statement can report the following error when used within the context of a stored procedure. The error is encountered when you attempt to create an object and then grant permission within the same procedure.

Msg 267, Level 16, State 1
Object 'xxx' cannot be found.

WORKAROUND

Use the EXECUTE statement to complete the operation.

For example:

create procedure dbo.spTest @strNewName varchar(8)
as
   select * into pubs.dbo.tblTest from master..sysusers
   grant select on pubs.dbo.tblTest to PUBLIC
go 

create procedure dbo.spTest @strNewName varchar(8)
as
   select * into pubs.dbo.tblTest from master..sysusers
   execute('grant select on pubs.dbo.tblTest to PUBLIC')
go 

STATUS

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

Keywords          : kbprg SSrvProg kbbug6.00 
Version           : 6.0
Platform          : WINDOWS 
Issue type        : 

Last Reviewed: March 26, 1999