January 22, 2016 at 4:26 pm
I've found several code examples which describe how to make this work, and they all have very simple instructions.
This is some code executed under my sysadmin account
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
Then I try & grant EXECUTE on all procedures in that database to a role, then grant that role to a user
USE trx_d
GO
CREATE ROLE db_executor
GRANT EXECUTE TO db_executor
ALTER ROLE db_executor ADD MEMBER [smithr]
GO
Then I log in as smithr, and I'm able to see the procedure in the information_schema table, but I cannot execute it
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
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
And here's something to add to the confusion ... when I switched the SSMS output to "Results to Text" so that I could copy/paste it into this posting, I started getting the following
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.
So, what originally looked like an inability to execute, turned into an ability to execute, with the subsequent error indicating that the object could not be found. I also tried changing the parameter values for the procedure to make sure that I was getting novel values for each execution, and I am.
--=Chuck
January 22, 2016 at 4:36 pm
Just as a follow up, I tried to create some sample code so that this could be reproducible, and using the following
create table junk (pk integer);
insert into junk values (1);
go
create procedure count_junk
as
set nocount on;
begin
select count(*) from junk;
end
exec count_junk;
go
I was able to execute this as smithr without any errors.
I tried removing the "dbo." from the 'proc_plan_get_count' execution request (and adding 'trx_d.dbo...' etc) and got the same results as my initial post.
Then I tried granting EXECUTE privileges directly to the smithr user, and received the same outcome as the initial post. So, it looks like it's not a rights issue per se, but some syntax issue?
It's also worth mentioning that I restored this database into MSSQL 2014 from 2008, and so in thinking that maybe there was an issue with that, I dropped and recreated 'proc_plan_get_count', although it didn't change anything in the output. (When I checked the compatibility level in SSMS, it was blank for this database.)
--=cf
January 25, 2016 at 1:57 pm
So, what I initially thought was a permissions issue at the database level (some problem granting EXECUTE on all procedures), still turned out to be a permissions issue. Had I showed you the source code, one of you would have seen it immediately. The original installer of the procedure forgot to place a GO in between the last line of the procedure, and a grant. The following is from SSMS, upon request to modify the procedure:
USE [trx_d]
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 stat as status_code, COUNT(*) as status_count
FROM *********
WHERE plan_year = @PlanYear AND
employee_id = @EmployeeId
GROUP BY stat
END
GRANT EXEC ON [dbo].proc_develop_plan_get_count TO trx_test
What's further interesting is that we're noticing this, as I'm switching this application over to using another instance login, and thus copying permissions from the prior account. But, the other account doesn't fail in the same way as my new account. It throws a warning, not an error, and so it must have some 'CAN GRANT PRIVILEGE' grant which causes this outcome:
specific_name
----------------------------------
proc_plan_get_count
status_code status_count
-------------------- ------------
Approved 1
Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.
January 25, 2016 at 4:56 pm
I sure am glad you got this. I looked at this post this past Saturday and I just couldn't see the problem. It bugged me enough to check it out again.
The reason you're getting the error is that you're the one creating it. When I create a procedure, I don't do it as the user that's going to be running the application. As the owner, I can grant permission to the procedure to the user who will be used to connect to the database. Since you're creating it, you obviously have permission to do so. As the owner, you don't need to grant yourself permission to run your procedure.
January 26, 2016 at 8:43 am
In this case, I was just creating a new account to access these procedures. When the Developer was testing the new account, these errors arose. The original creation of the procedure was by a Developer with db_owner access. They just neglected to exclude the GRANT statement from the definition of the procedure.
Also, I'm not sure if it was clear, but it's the execution of the procedure, and not the installation, which was throwing the error. The installation occurs without issue from a db_owner account (as I experienced, when I was tinkering around with it in it's various manifestations). The execution was from a less privileged account.
I still can't figure out why the old account was throwing a warning, and the new account was throwing an error, when the procedure was executed. I'm not sure that it matters in the grand scheme of things, but I'm still curious. I've tried several queries which collect up privileges by user, and there's no difference between my new account and the old, aside from the new account inheriting EXECUTE privileges from the db_executor role (via the approach described in my initial post above), and the old account having EXECUTE privileges granted directly to it for each individual procedure.
--=Chuck
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply