GRANT Execute - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

GRANT Execute

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2003-07-14 : 18:50:40

I've got two UDF.

GRANT EXECUTE ON dbo.LFM_First TO Public

works with no error, but

GRANT EXECUTE ON dbo.UDF_UserCompletions TO Public

returns the error:

Server: Msg 4606, Level 16, State 1, Line 1
Granted or revoked privilege EXECUTE is not compatible with object.

The 2nd UDF has 2 paramters, the first only one.

Any ideas?

Sam

nr
SQLTeam MVY

12543 Posts

Posted - 2003-07-14 : 18:55:22

These both work - can you post a bit more about the function that fails. What are the parameters and what does it return.

create function au
(@i int)
returns int
as
begin
return (select 1)
end

grant execute on dbo.au to public

create function au2
(@i int, @j int)
returns int
as
begin
return (select 1)
end

grant execute on dbo.au2 to public

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SamC
White Water Yakist

3467 Posts

Posted - 2003-07-14 : 19:36:08

CREATE FUNCTION dbo.LFM_Last (@LFM varchar (100))
-- Assumes name format is Last, First M. (period is optional)
RETURNS varchar (100)
AS
BEGIN
SET @LFM = RTRIM(LTRIM(@LFM)) -- Remove left / right blanks
SET @LFM = LEFT(@LFM, charindex(',', @LFM+',')-1) -- We have the last name
IF LEN(@LFM) > 2 AND SUBSTRING(@LFM, 2, 1) = ' ' AND SUBSTRING(@LFM, 3, 1) <> ' ' BEGIN -- May be missing an apostrophe
IF LEFT(@LFM,1) = 'D' OR LEFT(@LFM,1) = 'O' -- Like D'Almata or O'Riley
SET @LFM = LEFT(@LFM,1) + '''' + SUBSTRING(@LFM,3,100)
END
SET @LFM = REPLACE(@LFM, ' ', ' ') -- ELIMINATE DOUBLE SPACES LIKE LNAME JR
RETURN (@LFM)
END
GO
GRANT EXECUTE ON dbo.LFM_Last TO Public
GO

CREATE FUNCTION dbo.UDF_UserCompletions(@CourseID INT, @ClientID INT)
/*************************************************************************************************
Copyright © 2002 Native Intelligence, Inc. All rights reserved.

Purpose: Return Table of Active and Inactive User Records with Completion Records Joined

Written by: Sam Carter

Tested on: SQL Server 2000

Date modified: July 9, 2003

Email: sam@nativeintelligence.com

*************************************************************************************************/
returns @t table (CRecordID INT) -- Returns paired UserID, completion record
AS
BEGIN

INSERT INTO @t (CRecordID)
SELECT MIN(CRecordID) -- This select returns all course records with the same Credit as @CourseID
FROM CourseRecords C1
INNER JOIN Courses C3 ON C3.CourseID = C1.CourseID
LEFT OUTER JOIN CourseCredits CC ON CC.CourseID = C1.CourseID
WHERE (C1.CourseID = @CourseID
OR CC.CreditID = (SELECT CreditID FROM CourseCredits WHERE CourseID = @CourseID) )
AND C1.CRecordStart >= C3.CourseStartDate
AND C1.ClientID = @ClientID
GROUP BY C1.UserID

RETURN
END
GO

GRANT EXECUTE won't work on the 2nd.

Sam

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-07-14 : 20:00:02

Sam,

Think how different the two functions are.. one returns a scalar the other a table..

You EXECUTE scalar functions but SELECT table valued ones....

DavidM

"SQL-3 is an abomination.."

SamC
White Water Yakist

3467 Posts

Posted - 2003-07-14 : 20:03:09

I was wondering...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources