Results 1 to 3 of 3

Thread: [RESOLVED] Giving access to Excel only to users who are member of groups, calling UDF sql-server

  1. #1

    Thread Starter
    New Member
    Join Date
    Feb 2020
    Posts
    2

    Resolved [RESOLVED] Giving access to Excel only to users who are member of groups, calling UDF sql-server

    I'm creating a VBA code to execute on opening the excel workbook that gives a message ("not correct access" for example) and to close automatically if the user (defined by his logon user name) is not part of any of the groups, listed in the code. If a user is part of a group is defined by a scalar function in sql-server. If the current user is part of one of the groups listed, than do nothing - he/she should have normal access to the file, otherwise, return a message and force closing the file. I'm quite new to VBA and there're things that I might be missing in order to make this code work properly. Now, it gives me the message, and forces to close. Bur the VBA function, that should use my scalar sql function, returns -1, instead of 1 and 0. Could it be a syntax problem? I am dealing with this code for days and my research in the forums didn't help me so far. Any help will be appreciated!

    My function in sql-server is taking data from a table [user_list] that has a list of all users and their groups. The function returns 1 if the user is part of the group and 0 if not.

    Code:
    ALTER FUNCTION [dbo].[check_windows_group]
            ( @login_name  NVARCHAR(30)
    	, @group_name NVARCHAR(250)
    	)
    RETURNS BIT
    AS
            BEGIN
                    DECLARE @result BIT;
                    SELECT  @result = CASE WHEN EXISTS
                            (SELECT  login_name
                               FROM   [dbo].[user_list]
                               WHERE login_name = @login_name AND group_name = @group_name)  THEN 1
                                           ELSE 0
                                      END;
                    RETURN @result;
            END
    My VBA Solution so far is following

    Code:
    'macro will execute on opening the workbook
    Sub Auto_Open()
    
    Dim ObjWshNw As Object
    Set ObjWshNw = CreateObject("WScript.Network")
    Dim user As String
    user = ObjWshNw.UserName 'returns the logon username of the user
    
    Dim groupArr() As Variant
    groupArr = Array("G_LUE_ITP_DBA")
    
    flag = 0
    
    Dim group As Variant
    For Each group In groupArr
            
            Dim access As String
            access = giveAccess(user, group)
            'MsgBox access 'here returns -1
            If access = 1 Then
                flag = 1 'do nothing
            End If
    Next group
    
    If flag = 0 Then
          MsgBox Prompt:="The user does not have the correct access rights to view this workbook", Buttons:=vbExclamation
          ActiveWorkbook.Close
    End If
    
    
    End Sub
    
    
    Public Function giveAccess(user As String, group As Variant) As Integer
    
        Dim objConn As New ADODB.Connection
        Dim objRecordSet As New ADODB.Recordset
        
    'try and catch errors with server access
    On Error GoTo ErrHandler
    
    'establish ConnectionString
        With objConn
         .ConnectionString = "driver={SQL Server};database=name;Server=name; Trusted_Connection=Yes"
         .CommandTimeout = 40
         .Open
        End With
        
        objRecordSet.ActiveConnection = objConn
    
    'open the scalar function
        objRecordSet.Open ("SELECT [windows_user_group].[check_windows_group] ('" & user & "', '" & group & "')")
         
        giveAccess = objRecordSet.Fields.Item(0).Value
    
        objConn.Close
    
        Set conn = Nothing
        Set objRecordSet = Nothing
    
    Exit Function
    ErrHandler:
        MsgBox Prompt:="The user does not have the correct access rights to view this workbook", Buttons:=vbExclamation
        ActiveWorkbook.Close
    Resume Next
    
    End Function
    Last edited by nevena; Feb 14th, 2020 at 10:59 AM.

  2. #2
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: [RESOLVED] Giving access to Excel only to users who are member of groups, calling

    Well, a case for debugging.....
    Up front: I'm no expert for SQL-Server.
    The first thing i noticed:
    1) Your UDF's name is [dbo].[check_windows_group] but your call to it is [windows_user_group].[check_windows_group] --> as i said: No MSSQL-Expert here
    2) Check what values for UserName and Group (from the Group-Array) you get before the call to the UDF. Pay attention to Case-Sensivity (UserName "Zvoni"<>"zvoni")
    3) What values are arriving in the UDF? Maybe insert the arriving values in a test-table to look them up in SSMS
    4) Don't mix Data-Types. Your UDF returns a Bit, which you store in an integer giveaccess-function. In your Auto-Open "access" is string which gets assigned the return-value of integer.
    In general: If you expect a True/False don't use numeric values, use True/False with Boolean-Dataytype
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  3. #3

    Thread Starter
    New Member
    Join Date
    Feb 2020
    Posts
    2

    Re: [RESOLVED] Giving access to Excel only to users who are member of groups, calling

    Hi Zvoni,

    thanks for the comment! I actually finally resolved it yesterday
    1) this is a typo, you are right - I call my function the way it is named
    2) I don't know about VBA but SQL is case insensitive, therefore this wouldn't matter for the result of my UDF
    4) exactly this was my problem - mixing data types- the values from the UDF are 1 and 0 (BIT) for true and false, as there's no Boolean data type in SQL, and for some reason I needed to change the data type of the function giveAccess to Boolean, otherwise if Integer, it returns the values 0 and -1 (which corresponded to False and True). Now it works when using Boolean for my vba function

Posting Permissions

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



Click Here to Expand Forum to Full Width