find user role

[JEM]

Hello. I am trying to create a stored procedure to call from VBA code
to find out what role a user is a member of which will then trigger
other events. What system table do I find the text name of the role
in? I have started the following, which I snipped from another post,
but it isn't quite working...


CREATE PROCEDURE spCustomFindRole
(
@RoleName sysname
)

AS
BEGIN
DECLARE @RetVal Integer

IF IS_MEMBER(@RoleName) = "Admin"
SET @RetVal = 1
ELSE IF IS_MEMBER(@RoleName) = "Board"
SET @RetVal = 2
ELSE IF IS_MEMBER(@RoleName) = "Development"
SET @RetVal = 3
ELSE IF IS_MEMBER(@RoleName) = "Program"
SET @RetVal = 4
ELSE
SET @RetVal = 5



RETURN @RetVal
END

 

[Sylvain Lafontaine]

Would be probably easier for you to use the system stored procedures
sp_helpuser and sp_helplogins; possibly in association with a call to
SUSER_NAME() (for some limitations, see
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=51941 ).

However, you should ask this question in a newsgroup dedicated to sql-server
such as microsoft.public.sqlserver.server. m.p.s.server or m.p.s.security.

 

[JEM]

Thanks Sylvain. I figured it out.

Would be probably easier for you to use the system stored procedures
sp_helpuser and sp_helplogins; possibly in association with a call to
SUSER_NAME() (for some limitations, see
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=51941 ).

However, you should ask this question in a newsgroup dedicated to sql-server
such as microsoft.public.sqlserver.server. m.p.s.server or m.p.s.security.