January 7, 2011 at 10:11 am
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).
[font="Courier New"]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.
[/font]
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
January 7, 2011 at 11:23 am
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
January 7, 2011 at 11:41 am
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
September 27, 2011 at 8:25 am
Did you find a solution? Thanks.
October 5, 2011 at 7:45 pm
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.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply