Is it possible to restrict access to a pivot table on Analysis Services on Excel?

Question

I have an Excel file with a connection to an Analysis Services database that allows me to build a pivot table. To update this file, you need to be connected with a connection to Azurewith your email + password.

I would like to share this file with users so that they can update the pivot table but I don't want them to be able to modify it or have access to other information in the database.

Is there a simple solution? Directly on the Excel file or via a VBA script that would block this or make another sheet with the result of the pivot table?

Or the only solution is to put an Excel file containing the pivot table in a shared folder and give the users another Excel file that connects to it?

Thanks for your help

Answer 1

Can you try this and let me know if it works for you?

Sub ADOExcelSQLServer()
     ' Carl SQL Server Connection
     '
     ' FOR THIS CODE TO WORK
     ' In VBE you need to go Tools References and check Microsoft Active X Data Objects 2.x library
     '
     
    Dim Cn As ADODB.Connection
    Dim Server_Name As String
    Dim Database_Name As String
    Dim User_ID As String
    Dim Password As String
    Dim SQLStr As String
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
     
    Server_Name = "EXCEL-PC\SQLEXPRESS" ' Enter your server name here
    Database_Name = "NORTHWND" ' Enter your database name here
    User_ID = "" ' enter your user ID here
    Password = "" ' Enter your password here
    SQLStr = "SELECT * FROM [Customers]" ' Enter your SQL here
     
    Set Cn = New ADODB.Connection
    Cn.Open "Driver={SQL Server};Server=" & Server_Name & ";Database=" & Database_Name & _
    ";Uid=" & User_ID & ";Pwd=" & Password & ";"
     
    rs.Open SQLStr, Cn, adOpenStatic
     ' Dump to spreadsheet
    For iCols = 0 To rs.Fields.Count - 1
        Worksheets("Sheet1").Cells(1, iCols + 1).Value = rs.Fields(iCols).Name
    Next
    With Worksheets("sheet1").Range("a2:z500") ' Enter your sheet name and range here
        '.ClearContents
        .CopyFromRecordset rs
    End With
     '            Tidy up
    rs.Close
    Set rs = Nothing
    Cn.Close
    Set Cn = Nothing
End Sub

At some point, near the end, refresh your Pivot Table.

Sheet1.PivotTables("PivotTable1").PivotCache.Refresh

Or.

ThisWorkbook.RefreshAll