locked
MS Access Pass-Through Queries to SQL Server shows all system tables RRS feed

  • Question

  • Hi,

    We have several MS Access users whom we want access to a SQL Server 2014 Database in a server.  We want to limit their access to only one SQL Server database in the server.  They are all using MS Access but would access the SQL Server database using pass-through queries as described in

    http://www.mssqltips.com/sqlservertip/1482/microsoft-access-pass-through-queries-to-sql-server/

    They connect to the server via MS Access database, by selecting "External Data" menu and clicking the ODBC Database icon.  This displays the "Get External Data" dialog.  After specifying the dsn connection, it displays the "Import Objects" dialog.

    Our problem is, the Import Objects dialog shows all system tables in the server.  We want them to see only the one database they're supposed to access.

    Is this a permission issue?  We only granted them the following permissions:

    USE dbname;
    GRANT CREATE TABLE TO user1;
    GRANT INSERT ON DATABASE::dbname TO user1;
    GRANT DELETE ON DATABASE::dbname TO user1;
    GRANT UPDATE ON DATABASE::dbname TO user1;
    GRANT SELECT ON DATABASE::dbname TO user1;

    Not sure why MS Access shows all the system tables in the server as below:

    Appreciate any help.


    Marilyn Gambone


    • Edited by deskcheck1 Wednesday, May 20, 2015 1:07 PM
    Wednesday, May 20, 2015 1:06 PM

Answers

  • It's Access that is broken. It's querying sys.all_objects when it should query sys.objects instead.

    I don't see to be able to prevent the system views from being displaye. Well Ernest suggested

     DENY SELECT ON SCHEMA::SYS to {user}

    But now the query fails entirely, as the user lacks permission to run the query.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by deskcheck1 Thursday, May 21, 2015 1:06 PM
    Wednesday, May 20, 2015 9:23 PM

All replies

  • do you mean one table or one database -

    the list above - they are belong to one database..


    Hope it Helps!!

    Wednesday, May 20, 2015 1:32 PM
  • Hi,

    The image only showed the top portions of the tables. I'm attaching another image that shows the system tables that are visible in the Import dialog box which a user can actually download into Access.  One of them is the sys.server_permissions table.  I don't think this is safe and don't know why Access allows this.


    Marilyn Gambone

    Wednesday, May 20, 2015 2:40 PM
  • What you can do is this:

    DENY SELECT ON SCHEMA::SYS to {user}

    DENY SELECT ON SCHEMA::INFORMATION_SCHEMA TO {user}

    What you may also want to do is modify your grants such that you are not granting to the entire database, which includes sys objects by default, and on only grant permissions by schema:
    GRANT SELECT, INSERT, UPDATE, DELETE on schema::dbo to {user}

    With either of these 2 approaches, users will still be able to see the list of sys and information_schema objects, but will not be able to query them. But by granting select on the DATABASE, users can query all of these system tables.


    Wednesday, May 20, 2015 5:03 PM
  • Hello,

    Alternatively, you can use Database Roles and assign permissions and logins to them.

    http://www.sqlideas.com/2012/01/custom-database-role-in-sql-server.html



    Hope this helps.



    Regards,



    Alberto Morillo
    SQLCoffee.com

    Wednesday, May 20, 2015 5:17 PM
  • It's Access that is broken. It's querying sys.all_objects when it should query sys.objects instead.

    I don't see to be able to prevent the system views from being displaye. Well Ernest suggested

     DENY SELECT ON SCHEMA::SYS to {user}

    But now the query fails entirely, as the user lacks permission to run the query.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by deskcheck1 Thursday, May 21, 2015 1:06 PM
    Wednesday, May 20, 2015 9:23 PM
  • Hi,

    Thanks for all the replies...yeah, I tried revising the GRANT permissions as suggested by Ernest, but the sys tables still show.  So now we know it's Access end that's at fault.  I guess, we'll just have to live with this for now.


    Marilyn Gambone

    Thursday, May 21, 2015 1:06 PM