I have recently upsized an Access database to SQL Server 2008. When I try to create an ODBC connection using the wizard it all works fine and I can link to the database tables. When my users try to, they don't see the dbo tables, only the system tables.
I am presuming that this is a permissions issue, as I set the database up, and my connection works fine. The administrator set up all 4 users as Users on the database in SQL Server, so if i go to the Security tab under the database I can see these users. I have been told that this is not the best way to set it up because if we want to add more users we will have to add them to the Users in SQL Server.
So, can somebody please let me know why my users cannot see the database tables, and what the best way to set the database is. It will be multi-user and all will need to be able to link to it from MS Access.
Permissions and security are not subjects I really understand in SQL Server, so the most explicit and easy to follow instructions will win the points!