SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Grant Execute with Grant issue


Grant Execute with Grant issue

Author
Message
T Michael Herring
T Michael Herring
SSC-Addicted
SSC-Addicted (469 reputation)SSC-Addicted (469 reputation)SSC-Addicted (469 reputation)SSC-Addicted (469 reputation)SSC-Addicted (469 reputation)SSC-Addicted (469 reputation)SSC-Addicted (469 reputation)SSC-Addicted (469 reputation)

Group: General Forum Members
Points: 469 Visits: 599
Ok, this is getting a bit irritating... I have a role that I have created to allow our developers to create stored procedures and functions (T-SQL for the role to follow). Everything was working with no issues for a couple of days and now the user associated with the role is running into the following error when granting execute permissions on the newly created procedure to public (being used just for testing).

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


Create Role T-SQL:
IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'Developer' AND type = 'R')
DROP ROLE [Developer]
GO
-- Create the database role
CREATE ROLE [Developer] AUTHORIZATION [db_owner]
GO
-- Grant database level permissions to the role
GRANT ALTER ANY SCHEMA TO [Developer]
GO
GRANT CREATE FUNCTION TO [Developer]
GO
GRANT CREATE PROCEDURE TO [Developer]
GO
GRANT SHOWPLAN TO [Developer]
GO
GRANT VIEW DEFINITION TO [Developer]
GO
GRANT EXECUTE TO [Developer] WITH GRANT OPTION
GO



Stored Procedure T-SQL:
USE [dba_objects_TEST]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_dba_testing]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_dba_testing]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_dba_testing]
AS
BEGIN
SET NOCOUNT ON;

SELECT TOP 20 *
FROM dbo.DeadlockEvents;
END
GO
GRANT EXECUTE ON [dbo].[usp_dba_testing] TO [public];
GO



This was working just fine for a couple of days, but now is not, and as far as I can tell the required bits are in place for this to work properly. Anyone out there have any insight into this or see anything I'm missing? I've tried changing the
GRANT EXECUTE... WITH GRANT OPTION

to be at the database and also at the schema level and everything is being qualified with the schema names...

TIA
Mike

T. Michael Herring
Database Administrator
GSquared
GSquared
SSC Guru
SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)

Group: General Forum Members
Points: 94593 Visits: 9730
Is the login in use still associated with that database role?

Has there been any Deny command issued on any of the needed rights since the Grant was issued?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
T Michael Herring
T Michael Herring
SSC-Addicted
SSC-Addicted (469 reputation)SSC-Addicted (469 reputation)SSC-Addicted (469 reputation)SSC-Addicted (469 reputation)SSC-Addicted (469 reputation)SSC-Addicted (469 reputation)SSC-Addicted (469 reputation)SSC-Addicted (469 reputation)

Group: General Forum Members
Points: 469 Visits: 599
Yes, the user is still associated with the role, and no, there have been no denies added to the role or user.

T. Michael Herring
Database Administrator
yoffes
yoffes
SSC-Enthusiastic
SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)

Group: General Forum Members
Points: 155 Visits: 70
Did you find a solution? Thanks.
suneel kamavaram
suneel kamavaram
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1101 Visits: 614
When you are able to grant permission, Am sure definitely the users part of developer would have db_owner privileges.

I think there is limitation for the objects created under dbo schema when we grant execute privilege with 'with grant option' at database level not at object level.

I tried to create objects under user defined schema and able to grant execute privilege on proc in user defined schema to other user.

here are my test queries.

-- log on with login X which has default schema as userdefined_schema
create proc wgo_p1
as
begin
select @@servername
end

grant execute on [userdefined_schema].[wgo_p1] to newuser
-- sunccessful

to confirm this I have created one more schema userdefined_schema_wgo with authorization to login/user Y

create proc userdefined_schema_wgo.wgo_p1
as
begin
select @@servername
end

grant execute on [userdefined_schema_wgo].[wgo_p1] to newuser
-- fails when you execute with login X

and login X can't grant permission to newuser on proc userdefined_schema_wgo.wgo_p1


then authorization on schema userdefined_schema_wgo has been changed to X.

now login X can grant permission to newuser on proc userdefined_schema_wgo.wgo_p1


pre setup: I have granted all the privileges same as you as below:
CREATE ROLE [Developer] AUTHORIZATION [db_owner]
GO
-- Grant database level permissions to the role
GRANT ALTER ANY SCHEMA TO [Developer]
GO
GRANT CREATE FUNCTION TO [Developer]
GO
GRANT CREATE PROCEDURE TO [Developer]
GO
GRANT CREATE table TO [Developer]
go
GRANT SHOWPLAN TO [Developer]
GO
GRANT VIEW DEFINITION TO [Developer]
GO
GRANT EXECUTE TO [Developer] WITH GRANT OPTION
and login X is part of role Developer.

And thank you very much for the scenario, it helped me to explore most of the schema and with grant option stuff.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search