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

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')

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.