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

ID: Q151605


The information in this article applies to:
  • Microsoft SQL Server version 6.0

BUG#: 13040 (6.00)

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 

can be changed to:


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
© 2000 Microsoft Corporation. All rights reserved. Terms of Use.