-
Feb 14th, 2020, 08:33 AM
#1
Thread Starter
New Member
[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.
-
Feb 17th, 2020, 04:18 AM
#2
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
-
Feb 17th, 2020, 04:38 AM
#3
Thread Starter
New Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|