Restrict what tables users can see in SQL Server 2005
Posted on 2009-12-28
I have a database called demand_plan. In this database is about 70 tables/views. I have about 70 users worldwide currently that access the tables in this SQL Server 2005 database by linking to the tables in Access 2000. So SQL Server 2005 is the backend and Access 2000 is the frontend for the users.
Without changing the userid and password that is already setup on all these users systems, I would like to restrict what tables/views are available to the end users when they try to link to the database from Access 2000. In essence, there are about 20 tables they should be able to see. The list of what is available will frequently change, so I am hoping this is something simple where I can just go in and add tables/views as they are available, and not have to recreate this constantly.
I am new to SQL Server, so struggling with this, even though I would think it would be simple. I am not a stranger to programming or databases, just new to SQL Server, so past experience would tell me this is going to come down to some type of User permissions. I thought possibly also it could fall into the text of a schema, but many of these tables have the generic 'dbo' in front of them and a ton of programming already in place, so was hoping to avoid any changes there.