Query User Role 1

[MelissaKT]

I have created a couple of roles in my Sql DB. The roles are Auditor and Commission. I have a form called 'frmAdminApproval'. I would like to be able to lock or make invisible specific fields based on a user's group(role). Is it possible to query a user role?

 

[cmmrfrds]

You may need to look at more than 1 sys table. Start by looking in dbo.sysusers

There should be a sysusers in the database, but for additional sys tables like role you may need to look in the master database.

To find user login name use.
select suser_sname()

 

[MelissaKT]

First of all, THANKS!! Based on your input, I wrote a function - I'm sure that a stored procedure would have been better but I'm not as familiar with them. I attempted to user the 'select suser_sname()' that you suggested but it didn't work for me (again, I'm not as familiar with this as I am with vba) I found a function on this web site call fWin2KUserName (I can't remember who wrote it - but thanks to whomever it was) and that's what I've been using to get the user name. Here is the function:

Public Function ReturnUserGroup()
    Dim rst As ADODB.Recordset
    Dim strSQL As String
    Dim lngUserGroup As Long
    Set rst = New ADODB.Recordset
    strSQL = "SELECT gid FROM sysusers WHERE sysUsers.Name = '" & fWin2KUserName & "'"
    rst.Open strSQL, Application.CurrentProject.Connection, adOpenKeyset, adLockOptimistic
    With rst
        If rst.RecordCount > 0 Then
            lngUserGroup = rst!GID
        End If
    End With
    Select Case lngUserGroup
        Case 16400
            ReturnUserGroup = "AUDITOR"
        Case 16401
            ReturnUserGroup = "COMMISSION"
        Case 16402
            ReturnUserGroup = "USER"
        Case 16403
            ReturnUserGroup = "ADMIN"
        Case Else
            ReturnUserGroup = "NA"
    End Select
        
End Function

Thanks a bunch!

 

[cmmrfrds]

I think what you did was reasonable, there isn't that much overhead for a small table that is done only occassionaly.

This should have worked.
strSQL = "SELECT gid FROM sysusers WHERE sysUsers.Name = '" & suser_sname() & "'"

 

[MelissaKT]

For some reason, the suser_sname() isn't working for me - maybe I need a reference that I don't have? Anyway, here is a star - I really appreciate your help!!

 

[cmmrfrds]

Can you try one thing. Since it is returned from a sql server function it may not need to be in quotes.

strSQL = "SELECT gid FROM sysusers WHERE sysUsers.Name = " & suser_sname()

 

[MelissaKT]

No, still not working. Is this a built-in function that I should be able to run?

 

[danvlas]

strSQL = "SELECT gid FROM sysusers WHERE sysUsers.Name = suser_sname()"

suser_sname() is an SQL function that VB wouldn't recognize, so you have to wrap it within the query string.

HTH


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[MelissaKT]

COOL!! It worked!! Thanks to both of you for your help!