InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 20 May 2013 13:02:09 GMT20http://www.sqlservercentral.com/Forums/Topic1044540-359-1.aspxWhen 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_schemacreate proc wgo_p1asbeginselect @@servernameendgrant execute on [userdefined_schema].[wgo_p1] to newuser-- sunccessfulto confirm this I have created one more schema userdefined_schema_wgo with authorization to login/user Ycreate proc userdefined_schema_wgo.wgo_p1asbeginselect @@servernameendgrant execute on [userdefined_schema_wgo].[wgo_p1] to newuser-- fails when you execute with login Xand login X can't grant permission to newuser on proc userdefined_schema_wgo.wgo_p1then 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 roleGRANT ALTER ANY SCHEMA TO [Developer]GOGRANT CREATE FUNCTION TO [Developer]GOGRANT CREATE PROCEDURE TO [Developer]GOGRANT CREATE table TO [Developer]goGRANT SHOWPLAN TO [Developer]GOGRANT VIEW DEFINITION TO [Developer]GOGRANT 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.Wed, 05 Oct 2011 19:45:47 GMTsuneel kamavaramhttp://www.sqlservercentral.com/Forums/Topic1044540-359-1.aspxDid you find a solution? Thanks.Tue, 27 Sep 2011 08:25:57 GMTyoffeshttp://www.sqlservercentral.com/Forums/Topic1044540-359-1.aspxYes, the user is still associated with the role, and no, there have been no denies added to the role or user.Fri, 07 Jan 2011 11:41:37 GMTT Michael Herringhttp://www.sqlservercentral.com/Forums/Topic1044540-359-1.aspxIs 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?Fri, 07 Jan 2011 11:23:12 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic1044540-359-1.aspxOk, 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).[font="Courier New"][color="#FF0000"]Msg 15151, Level 16, State 1, Line 1Cannot find the object 'usp_dba_testing', because it does not exist or you do not have permission.[/color][/font]Create Role T-SQL:[code="sql"]IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N'Developer' AND type = 'R') DROP ROLE [Developer]GO-- Create the database roleCREATE ROLE [Developer] AUTHORIZATION [db_owner]GO-- Grant database level permissions to the roleGRANT ALTER ANY SCHEMA TO [Developer]GOGRANT CREATE FUNCTION TO [Developer]GOGRANT CREATE PROCEDURE TO [Developer]GOGRANT SHOWPLAN TO [Developer]GOGRANT VIEW DEFINITION TO [Developer]GOGRANT EXECUTE TO [Developer] WITH GRANT OPTION GO[/code]Stored Procedure T-SQL:[code="sql"]USE [dba_objects_TEST]GOIF 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]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE [dbo].[usp_dba_testing]ASBEGIN SET NOCOUNT ON; SELECT TOP 20 * FROM dbo.DeadlockEvents;ENDGOGRANT EXECUTE ON [dbo].[usp_dba_testing] TO [public];GO[/code]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 [code="sql"]GRANT EXECUTE... WITH GRANT OPTION[/code] to be at the database and also at the schema level and everything is being qualified with the schema names...TIAMikeFri, 07 Jan 2011 10:11:49 GMTT Michael Herring