June 28, 2004 at 2:55 am
Dear All,
Urgent help needed, I have a stored function which tests membership of a role using the Is_Member function.
The problem is that no matter which role a user is a member of, 2 is always returned.
CREATE FUNCTION dbo.sfFindUserRoles ()
RETURNS int AS
BEGIN
DECLARE @intRetval int
IF IS_MEMBER ('ARMS_Analysts') = 1
BEGIN
set @intRetval = 1
END
IF IS_MEMBER ('ARMS_IT_Admins') = 1 OR IS_MEMBER ('db_owner') = 1
BEGIN
set @intRetVal = 2
END
IF IS_MEMBER ('ARMS_ReadOnly_User') = 1
BEGIN
set @intRetVal = 3
END
RETURN (@intRetval)
END
Any help?
June 29, 2004 at 1:49 am
What's the goal of your function ?
- returning the "highest" membership ?
- returning the "last in sequence" membership ?
(your current function)
- returning the "first in sequence" membership ?
maybe this explains what I mean.
CREATE FUNCTION dbo.sfFindUserRoles ()
RETURNS int AS
BEGIN
set nocount on
DECLARE @intRetval int
IF IS_MEMBER ('TestRole1') = 1
BEGIN
set @intRetval = 1
END
else
begin
IF IS_MEMBER ('db_owner') = 1
BEGIN
set @intRetVal = 2
END
else
begin
IF IS_MEMBER ('TestRole2') = 1
BEGIN
set @intRetVal = 3
END
end
end
RETURN (@intRetval)
END
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 29, 2004 at 7:23 am
In CASE you want another way.
CREATE FUNCTION dbo.sfFindUserRoles ()
RETURNS int AS
BEGIN
set nocount on
DECLARE @intRetval int
SET @intRetVal = CASE WHEN IS_MEMBER ('ARMS_Analysts') = 1 THEN 1
WHEN IS_MEMBER ('ARMS_IT_Admins') = 1 THEN 2
WHEN IS_MEMBER ('db_owner') = 1 THEN 2
WHEN IS_MEMBER ('ARMS_ReadOnly_User') = 1 THEN 3
ELSE 0 --- default if not a member of any
END
RETURN (@intRetval)
END
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply