Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Grant Execute with Grant issue Expand / Collapse
Author
Message
Posted Friday, January 7, 2011 10:11 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 1:22 PM
Points: 261, Visits: 449
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
Wells Fargo
Post #1044540
Posted Friday, January 7, 2011 11:23 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, December 18, 2014 9:58 AM
Points: 13,872, Visits: 9,600
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
Post #1044580
Posted Friday, January 7, 2011 11:41 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 1:22 PM
Points: 261, Visits: 449
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
Wells Fargo
Post #1044597
Posted Tuesday, September 27, 2011 8:25 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, July 19, 2013 12:57 PM
Points: 17, Visits: 70
Did you find a solution? Thanks.
Post #1181802
Posted Wednesday, October 5, 2011 7:45 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 2:39 PM
Points: 637, Visits: 567

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.
Post #1186311
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse