GRANT support in MS ACCESS

Question

ALL,

Does GRANT-ing permissions supported in MS ACCESS?

I am connecting to accdb with ODBC driver from C++ app and trying to issue a GRANT command, but getting an error saying expecting DELETE, INSERT, PROCEDURE, SELECT or UPDATE.

It is on Windows 8.1 with MSVC 2017 Community.

The command I'm trying is:

GRANT SELECT ON MSysObjects TO Admin;

TIA!!

void uc_to_str_cpy(SQLWCHAR *dest, const std::wstring &src)
{
    const wchar_t *temp = src.c_str();
    while( *dest )
    {
        dest++;
    }
    while( *temp )
    {
        *dest = *temp;
        dest++;
        temp++;
    }
    *dest++ = 0;
    *dest = 0;
}

std::wstring query8 = L"GRANT SELECT ON MSysObjects TO Admin;";
query = new SQLWCHAR[query8.length() + 2];
memset( query, '\0', query8.length() + 2 );
uc_to_str_cpy( query, query8 );
ret = SQLExecDirect( m_hstmt, query, SQL_NTS );
delete[] query;
query = NULL;
if( ret != SQL_SUCCESS && ret != SQL_SUCCESS_WITH_INFO )
{
    GetErrorMessage( errorMsg, 1 );
    result = 1;
}

user_10527 · Answer

Grant does exist for Access. In most cases you can query against MSYSobjects. However, you could try several things:

First, try running your program (.exe) with elevated rights (right click and run as admin).

next up. You don't mention if this is JET (mdb) format, or is ACE (accdb format).

HOWEVER - using ODBC (as opposed to the oleDB driver), to my knowledge DOES NOT support Grant. So, quite sure you are out of luck.

You could I suppose consider creating a ADO object.

I find that EVEN inside of Access, if I use the built in ADO object, then grant will work.

but if I use a straight DAO object, then it does not work.

and for ODBC? Well, I find that grant does not seem to be supported - so, this looks like ODBC does NOT support the grant.

Also, use of GRANT in most cases also means that the connection string needs to include the work group file (it is by default automatic opened - even with ODBC, but using oleDB against such a database (from .net), I again find that GRANT does execute with oleDB - but gives an error message about the workgroup file having not been specified.

However, with ODBC - I get a error - syntax. So, at this point? ODBC does not support the grant DDL.

GRANT support in MS ACCESS

1 Answers