Microsoft KB Archive/151605

{|
 * width="100%"|

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

 * }

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 : kbprogramming

Issue type :

Technology : kbSQLServSearch kbAudDeveloper kbSQLServ600