2

I have a ODBC Linked table in Microsoft Access which is connected to sql server.

For some users the login which connect access to SQL Server has access to only one database with db_datareader role so they cannot edit any data in the tables. For other users they have db_datareader + db_datawriter role and they can edit any data.

How can I check in vba that my table is not editable in case of db_datareader logins?

Omid Sadeghi
  • 675
  • 7
  • 16

1 Answers1

3

You can use passthrough queries to get user role membership, and querydefs to create or access them:

Public Function is_datawriter() As Boolean
    Dim qdef As DAO.QueryDef
    Dim rst As DAO.Recordset

    Set qdef = CurrentDb.CreateQueryDef("")
    qdef.Connect = "ODBC; MY_ODBC_CONN_STRING"
    qdef.SQL = "SELECT IS_ROLEMEMBER('db_datawriter')"
    Set rst = qdef.OpenRecordset(dbOpenDynaset)
    If rst.Fields(0).Value = 1 Then is_datawriter = True
End Function

Testing table-specific rights is somewhat more difficult, but in your case this will probably do.

Erik A
  • 31,639
  • 12
  • 42
  • 67
  • your answer works and solves my problem but I wonder why the select statement always returns 1 when I execute it on SQL Server Management Studio no matter what login I use. – Omid Sadeghi May 10 '17 at 07:04
  • Don't know that, it shouldn't as far as I know – Erik A May 11 '17 at 06:38