Using SQL Server Application Roles with Linked Tables

[DonS100]

Hello, I'm trying to have my Access application that has linked tables use SQL Server Application Role feature. I have compiled my file into a mde file and hide the database window so the user only has access to the data though the forms. I want to use Application Roles
because otherwise any user with Query Analyzer can gain full access to the database with their windows Authorization login.

I provided my current code below,
basically it drops all the existing linked tables and then relinks them.

I know that their is a stored procedure called sp_SetAppRole.
Do I have to call this procedure for each table I'm linking or is using Application Roles not
possible with Access?

Thanks


Public Sub DBConnect(sServerName As String, sDBName As String)

Dim sSQL As String
Dim TableName As String
Dim locDB As localDB
Dim rs As Recordset
Dim MyDB As Database
Dim td As TableDef

DoCmd.Hourglass True
SQLConnectSERVER = sServerName
SQLConnectDatabase = sDBName

sServerName = "SERVER=" & sServerName & ";"
sDBName = "DATABASE=" & sDBName & ";"

Set MyDB = DBEngine.Workspaces(0).Databases(0)

sSQL = "Select * From qryDBConnections WHERE Connect Like '*" &
sServerName & "*' AND Connect Like '*" & sDBName & "*'"
Set rs = MyDB.OpenRecordset(sSQL)

If Not rs.EOF Then

SQLCONNECTSTRING = "ODBC" & _
";DRIVER={SQL SERVER}" & _
";SERVER=" & SQLConnectSERVER & _
";AppName=" & gsAppName & _
";DATABASE=" & SQLConnectDatabase & _
";TRUSTED_CONNECTION=Yes;"

SQLParamQryString = "DRIVER={SQL SERVER}" & _
";SERVER=" & SQLConnectSERVER & _
";AppName=" & gsAppName & _
";DATABASE=" & SQLConnectDatabase & _
";TRUSTED_CONNECTION=Yes;"

RelinkAllPassThroughQueries

'Cycle through any and all linked tables and remove connection
sSQL = "Select * From qryDBConnections"
Set rs = MyDB.OpenRecordset(sSQL)

Do Until rs.EOF
Debug.Print "Delete " & rs!TableName
On Error Resume Next
DoCmd.DeleteObject acTable, rs!TableName
On Error GoTo Err_Routine
rs.MoveNext
Loop
''''''''''''''''''''
'Cycle through all tables that should be attached and create linked tables
sSQL = "select * from lstTables ORDER BY TableName"
Set rs = MyDB.OpenRecordset(sSQL)

Do Until rs.EOF
Set td = MyDB.CreateTableDef(rs!TableName)
td.Connect = SQLCONNECTSTRING
td.SourceTableName = rs!TableName
MyDB.TableDefs.Append td
If Not IsNull(rs!UniqueIdentifiers) Then
'debug.print rs!TableName
sSQL = "CREATE UNIQUE INDEX PK_" & rs!TableName & " on " &
rs!TableName & "(" & rs!UniqueIdentifiers & ")"
MyDB.Execute sSQL
End If

rs.MoveNext
Loop


End Sub

 

[psemianonymous]

My understanding is that you set the role for the user on SQL Server, otherwise the users won't be able to access the data.

In other words, from Access, go ahead and leave all the passthrough queries and linked tables, even if that user doesn't have access to them.

In other words, Access shouldn't have anything to do with SQL Server security.