CODEWITHSUNDEEP
sql-serverstored-procedures
Starsky
Starsky

Reputation: 31

Getting a result, and an error

I'm using SQL Server 2014.

I have granted EXECUTE on a stored procedure to a user account, but when the sp is run, I get both a result and an error. This is from SSMS, with "Results to Text" selected:

set nocount on
use trx_d
go

select specific_name
  from information_schema.routines 
 where routine_type = 'PROCEDURE'
   and specific_name = 'proc_plan_get_count';
go

exec dbo.proc_plan_get_count 225, 2016;
go

specific_name
----------------------------------
proc_plan_get_count

status_code          status_count
-------------------- ------------
Approved             1

Msg 15151, Level 16, State 1, Procedure proc_plan_get_count, Line 25
Cannot find the object 'proc_plan_get_count', because it does not exist or you do not have permission.

I tried dropping and recreating the sp (which just contains a SELECT statement with a WHERE clause reflecting the parameters being passed in). I thought that might clear the cobwebs, since when I tried to create a new, simple procedure I had no problems. The drop & create had no effect, with the same behavior above appearing.

I changed the contents of the sp to avoid any table references, ran it and I received no error. Putting back in the SELECT stmt caused the issue to re-surface. This user account is in the db_datareader role, though (and you can see in the output above that it is successfully reading the table).

Then I tried just recreating the procedure under a new name (I added a "2" at the end), and when running it I received no errors.

I am thoroughly confused, thanks for any help on figuring this out,

--=Chuck

Upvotes: 2

Views: 38

Answers (1)

Starsky
Starsky

Reputation: 31

Being new to SQL Server, this one escaped me for a bit. Here's the gist of the code when I request to modify it through SSMS:

USE [trx_d]
GO
/****** Object:  StoredProcedure [dbo].[proc_plan_get_count]    Script Date: 1/25/2016 12:36:49 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[proc_plan_get_count] 
    -- Add the parameters for the stored procedure here
    @EmployeeId int = 0, 
    @PlanYear int = 0
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    SELECT stdcode as status_code, COUNT(*) as status_count
        FROM **********
        WHERE yr = @PlanYear AND
              empnum = @EmployeeId
        GROUP BY stdcode

END

GRANT EXEC ON [dbo].proc_plan_get_count TO *****

So, since there's no GO after the END keyword, that GRANT stmt is actually part of the source code, and was the source of the error upon sp execution.

Why did we hit it now? We were switching to a new database login, and while the new login looks to have all of the same privileges as the old login, there must be some other privilege that the old login has which doesn't balk at the GRANT EXEC step. It throws a warning instead of an error:

status_code          status_count
-------------------- ------------
Approved             3

Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.

Upvotes: 1

Related Questions