"Grantor does not have GRANT permission"
I ran into this error today, and found the description of the problem interesting enough to document.
In almost all cases, I use DataReaders to process results from stored procedures. Generally the code goes something like:
'do the code that sets up the connection and the command and stuff...
Dim oReader As SqlDataReader = oCommand.ExecuteReader()
If oReader.HasRows Then
Do While oReader.Read
'Process the data...
Loop
End If
But today I was calling a proc that returned more than one record set. Which turned the above into something like this:
'do the code that sets up the connection and the command and stuff...
Dim oReader As SqlDataReader = oCommand.ExecuteReader()
Do
If oReader.HasRows Then
Do While oReader.Read
'Process the data...
Loop
End If
Loop While oReader.NextResult
It's just a nested loop - and just like how the .Read() method returns False when there's no more rows to read, the .NextResult() method returns False when there's no more record sets to go through - except that it's already pointing at the first one, so you need to post-increment instead of pre-increment (to use the terms loosely).
In my test code, I knew I was calling a proc that returned two record sets - and so second time it reached 'Loop While oReader.NextResult', it should get a False return and move on to the cleanup code. But instead, an exception was thrown and execution moved into the Catch block that you can see here. The exception message was 'Grantor does not have GRANT permission'. And this had me confused.
A little bit of tracking down later I had the problem sorted. In the script file which created the stored procedure, the guy who wrote it left out a GO statement. Normally you would have this:
IF EXISTS (
SELECT * FROM dbo.sysobjects
WHERE id = OBJECT_ID(N'[dbo].[prProc]')
AND OBJECTPROPERTY(id, N'IsProcedure') = 1
)
DROP PROCEDURE [dbo].[prProc]
GO
CREATE PROCEDURE dbo.prProc
@SomeParam INTEGER
AS
SET NOCOUNT ON
SELECT something FROM somewhere
GO
GRANT EXECUTE ON prProc TO SomeOne
GO
What we had was this:
IF EXISTS (
SELECT * FROM dbo.sysobjects
WHERE id = OBJECT_ID(N'[dbo].[prProc]')
AND OBJECTPROPERTY(id, N'IsProcedure') = 1
)
DROP PROCEDURE [dbo].[prProc]
GO
CREATE PROCEDURE dbo.prProc
@SomeParam INTEGER
AS
SET NOCOUNT ON
SELECT something FROM somewhere
GRANT EXECUTE ON prProc TO SomeOne
GO
The GO was left out between the end of the stored procedure and the GRANT statement - which made the GRANT execute every time the proc was called - which the calling user didn't have permission to do.
Interestingly, a failed GRANT call is obviously not a batch-killer - the first two record sets were returned and processed, and it was only when I moved past that last record set did the error get raised - as if the error was attached to a phantom 3rd record set :)