Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2003
    Posts
    130

    Question Unanswered: Find out if current user is member of a role

    I need a stored procedure to find out if the current user is a member of a certain role.

    I want to pass the role name and return a bit to tell whether he is a member or not.

    I have this code to list the groups the current user is a member of but I don't know how to search or do a "SELECT FROM" on the results.
    Code:
    DECLARE @usr varchar(32) 
     
    SET @usr = USER
     
    EXEC sp_helpuser @usr
    But if the current user is a member of more than one role it returns multiple rows. So if I could do something like:

    DECLARE @grpName varchar(32)

    SELECT * FROM (EXEC sp_helpuser @usr) WHERE GroupName=@grpName
    IF rowcount > 0 THEN
    RETURN 1
    ELSE
    RETURN 0
    END IF

    I know that doesn't work so how can I do this?

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245
    I'm sure that someone out there can do better than this, but you might try:

    Code:
    ALTER PROC spCheckGroup
    
    @UserName varchar(255), @GroupName varchar(255)
    
    AS 
    
    DECLARE @Count int
    
    SELECT @Count = Count(*) 
    FROM (
    select 
    	s1.name as username,
    	s2.name as groupname
    from 
    	dbo.sysusers s1 left join dbo.sysmembers sm on
    		s1.uid = sm.memberuid
    	left outer join dbo.sysusers s2 on
    		sm.groupuid = s2.uid
    where
    	s1.uid < 16383
    ) t1
    WHERE 
       t1.userName = @UserName and
       t1.GroupName = @GroupName
    
    If @Count > 0
       Return 1
    ELSE
       Return 0
    Test it with this code:

    [/code]
    DECLARE @return_status int
    EXEC @return_status = spCheckGroup 'OAJO-SQLAdmin', 'db_owner'
    SELECT 'Return Status' = @return_status
    [/code]
    Last edited by hmscott; 08-05-04 at 13:46.
    Have you hugged your backup today?

  3. #3
    Join Date
    Apr 2004
    Location
    LA, CA
    Posts
    125
    check: BOL

    IS_MEMBER ( { 'group' | 'role' } )

  4. #4
    Join Date
    Dec 2002
    Posts
    1,245

    Talking

    I'll just crawl back under the rock where I came from....
    Have you hugged your backup today?

  5. #5
    Join Date
    Mar 2003
    Posts
    130
    LOL.

    Thanks for trying.

  6. #6
    Join Date
    Mar 2003
    Posts
    130
    I need a stored procedure to work with .NET so here's what I have:
    Code:
    CREATE PROCEDURE IsGroupMember
    (
    	@groupName nvarchar(32),
    	@retVal bit OUTPUT
    )
    AS
    	SET @retVal = IS_MEMBER(@groupName)
    GO
    and in Query Analyzer I run this:
    Code:
    DECLARE @bt bit
     
    EXEC IsGroupMember 'db_owner', @bt
     
    IF @bt = 1 print 'member'
    ELSE IF @bt = 0 print 'non-member'
    ELSE print 'undefined'
    but I keep getting undefined. What's wrong?
    Last edited by wey97; 08-05-04 at 14:27.

  7. #7
    Join Date
    Apr 2004
    Location
    LA, CA
    Posts
    125
    EXEC IsGroupMember 'db_owner', @bt output

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •