Access to SQL server migration

I am in the process of migrating 40 access databases to SQL Server. I am migrating the tables to the backend. All of these databases are Identical in structure and purpose. The goal is to centralize all of the databases backends into one Sql server database, but the data needs to stay seperated by each location. So I added a location field to each table in the database; so that each locations data will be seperated by location. How do I go about filtering out the the data so that each location can only update, delete, an view their own data? (I dont mind a long answer.)

[583 byte] By [GetCode] at [2007-12-24]

«« SQL Server Enterprise
»» Distributing .NET assembly for interop with VB6

# 1

Hi,

If the data is seperated into different tables based on regions, then you can control access to that table using group based priviledges and add the appropriate users to that group. However, it sounds like the data is interspersed to various tables and you're using a column to keep track of the location for each row. In this case, I'm not sure what the best way to control access to the data at the row level. I'll move this thread to the security forum -- Maybe someone there has an idea.

Il-Sung.

Il-SungLee-MSFT at 2007-8-31 >