I don't know enough about the issue you're having, but I thought I'd share a brief walk-through (as best I can recall) of a recent successful setup of a SQL Server on a local server and users accessing through Microsoft Access on workstations in the same office, just in case you might see a step in here and think, "oh, I didn't do that!" 🙂

(BTW, I'm basing this on the assumption that the user is trying to see the tables in Microsoft Access, because the subject line implied that. Most of these instructions apply without that, though... at least up until the "ODBC" step.)

- Installed the SQL Server on the server. Can't recall exactly what services I set it up under, unfortunately, and I can't check right now. It most likely matters, though, so you may want to check that, if you haven't already. I set it up with Windows Level Authentication.

- Restored the database (from development) to the server.

- In SSMS, went into Logins on the server level (under Security), and added the logins (in the case of my database, we wanted all legitimate domain users to have read access, so I set up a login for BUILTIN/USERS, as well as individual logins for people who needed more specialized access.)

- Then I went into the database and set up the Users there as well (under Security), making sure that each User was connected to a Login. This is where I added them to the roles that they belonged to.

- At this point, if a user had sat down at the server machine, and logged in as him/herself, then went into SSMS, they would have been able to see the tables. But nobody that's not physically at that machine would be able to see the SQL Server at all. So I configured the Windows firewall to allow traffic on a specific port according to the instructions here: https://msdn.microsoft.com/en-us/library/cc646023.aspx#BKMK_dynamic_ports and here: https://msdn.microsoft.com/en-us/library/ms177440.aspx. I've learned through painful experience that dynamic ports and SQL Browser are evil; things seem to work much better when you configure the server to use a specific port. That's just been my experience.

- On each workstation, I set up ODBC connections that are configured to connect to the specific port that I set up in the SQL Configuration Manager (see previous step) and the firewall. Also made sure the default database was set to the right database in the ODBC connection... as opposed to being set to connect to "master," which is the default setup. That might be the problem right there, if they're only seeing system tables... Oh, and be sure you're using the right version of the ODBC connector. Usually, the right version is the 32-bit version. If after setting it up, you're able to see it as an option when using the Linked Table Manager in Access, then you're using the right version. Also, I did all this while logged into the workstation as the domain administrator (not sure if that makes a difference).

That's it. At that point they were able to connect to the database. That's the best I can recall. Hope it helps you at all. 🙂