November 28, 2003 at 5:47 am
Hi
When I run sp_helprotect for the UDF below, I get the following error:
Server: Msg 170, Level 15, State 1, Line 15
Line 15: Incorrect syntax near ')'.
The UDF works fine in every other aspect, I just can't get its permissions.
ALTER FUNCTION dbo.udfUSR_PermissionListByEmployeeID
(@EmployeeID uniqueidentifier)
RETURNS TABLE
AS
RETURN SELECT Uugs.StationID,
CASE ViewScope WHEN 2 THEN Empl.EmployeeID ELSE NULL END AS EmployeeID
FROM dbo.EMP_Employees Empl
INNER JOIN dbo.USR_Employees_UserGroups Ueug ON Empl.EmployeeID = Ueug.EmployeeID
INNER JOIN dbo.USR_UserGroups Uugs ON (Ueug.UserGroupID = Uugs.UserGroupID)
WHEREEmpl.EmployeeID = @EmployeeID
ANDViewScope > 0
UNION
SELECT Stns.StationID, NULL AS EmployeeID
FROM dbo.EMP_Employees Empl
INNER JOIN dbo.USR_Employees_UserGroups Ueug ON Empl.EmployeeID = Ueug.EmployeeID
INNER JOIN dbo.USR_UserGroups Uugs ON (Ueug.UserGroupID = Uugs.UserGroupID)
CROSS JOIN dbo.STN_Stations Stns
WHEREEmpl.EmployeeID = @EmployeeID
ANDViewScope = 0
Brgds
Jonas
BrgdsJonas
December 2, 2003 at 8:00 am
This was removed by the editor as SPAM
December 2, 2003 at 8:41 am
Hi Jonas,
could you post the statement that's causing error? If it is just "sp_helprotect udfUSR_PermissionListByEmployeeID", I don't know what the problem could be.
I tested various possibilities and got the same error message as you when I wrote the function name with empty parentheses : sp_helprotect udfUSR_PermissionListByEmployeeID(). Could this be it?
Regards,
Vladan
December 2, 2003 at 9:08 am
The statement is as follows:
sp_helprotect 'udfUSR_PermissionListByEmployeeID'
The current permissions for this objects is one role having SELECT permission.
I did a workaround by directly querying the syspermissions table instead which works fine.
Brgds
Jonas
BrgdsJonas
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply