June 5, 2009 at 8:28 am
A user has reported a problem with a stored proc, where the message "Execute permission denied on object [stored proc name], database Test1 owner dbo"
They have a script that has been supplied to them that apparently should be used to set the permissions:
IF NOT EXISTS (SELECT name FROM sysusers WHERE name = N'FredGRP')
EXECUTE sp_addrole 'FredGRP'
GO
DECLARE @Statement varchar(4000)
DECLARE Grant_Cursor CURSOR FOR
SELECT 'GRANT SELECT,UPDATE,INSERT,DELETE ON [' + CONVERT(VARCHAR(255),NAME) + '] TO FredGRP'
FROM sysobjects
WHERE xtype IN ('U', 'V') and uid = 1
SET NOCOUNT ON
OPEN Grant_Cursor
FETCH NEXT FROM Grant_Cursor INTO @Statement
WHILE (@@FETCH_STATUS <> -1) BEGIN
EXEC (@Statement)
FETCH NEXT FROM Grant_Cursor INTO @Statement
END
DEALLOCATE Grant_Cursor
DECLARE Grant_Cursor CURSOR FOR
SELECT 'GRANT EXECUTE ON [' + CONVERT(VARCHAR(255),NAME) + '] TO FredGRP'
FROM sysobjects
WHERE xtype IN ('P', 'X', 'FN', 'IF')
SET NOCOUNT ON
OPEN Grant_Cursor
FETCH NEXT FROM Grant_Cursor INTO @Statement
WHILE (@@FETCH_STATUS <> -1) BEGIN
EXEC (@Statement)
FETCH NEXT FROM Grant_Cursor INTO @Statement
END
DEALLOCATE Grant_Cursor
DECLARE Grant_Cursor CURSOR FOR
SELECT 'GRANT SELECT ON [' + CONVERT(VARCHAR(255),NAME) + '] TO FredGRP'
FROM sysobjects
WHERE xtype IN ('TF')
SET NOCOUNT ON
OPEN Grant_Cursor
FETCH NEXT FROM Grant_Cursor INTO @Statement
WHILE (@@FETCH_STATUS <> -1) BEGIN
EXEC (@Statement)
FETCH NEXT FROM Grant_Cursor INTO @Statement
END
DEALLOCATE Grant_Cursor
GO
But the response from this is along these lines:
" Server: Msg 4606, Level 16, State 1, Line 1
Granted or revoked privilege EXECUTE is not compatible with
object.
Server: Msg 4606, Level 16, State 1, Line 1
Granted or revoked privilege EXECUTE is not compatible with
object."
....repeated several times.
This isn't the type of thing I've had to look at before. What do I need to check and what would be a good 'plan of attack' to get to the bottom of it?
Thanks,
BrainDonor
June 9, 2009 at 11:07 am
Instead of trying to executethe dynamic SQL, try to print it by using the PRINT command. Then check if there is anything wrong with the Grant statements that has been printed.
-Roy
March 31, 2010 at 2:47 pm
SELECT 'GRANT EXECUTE ON [' + CONVERT(VARCHAR(255),NAME) + '] TO FredGRP'
FROM sysobjects
WHERE xtype IN ('P', 'X', 'FN', 'IF')
The "EXECUTE" can NOT be granted on IF (inline table-valued-function).
March 31, 2010 at 3:08 pm
Yes, table valued function can be given select permission and not execute.
March 31, 2010 at 3:16 pm
Actually, this looks right and makes sense as TVF are used like tables and views. Not something I had really considered.
April 1, 2010 at 1:08 am
pureaqua (3/31/2010)
SELECT 'GRANT EXECUTE ON [' + CONVERT(VARCHAR(255),NAME) + '] TO FredGRP'FROM sysobjects
WHERE xtype IN ('P', 'X', 'FN', 'IF')
The "EXECUTE" can NOT be granted on IF (inline table-valued-function).
I was about to deny any knowledge of this post - then I realised it was 9 months ago!
Don't do that to me - I have enough trouble remembering last week:-P.
If I recall correctly I wrote a script that did something along those lines eventually, but its all back in the mists of time now.
Thanks for your time anyway.
BrainDonor.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply