locked
Access to SQL server migration RRS feed

  • Question

  • 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.)

    Tuesday, September 19, 2006 1:45 PM

Answers

All replies

  • 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.

    Tuesday, September 19, 2006 4:21 PM
  • Have a look at the following thread:

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=679308&SiteID=1

    Thanks
    Laurentiu

    Tuesday, September 19, 2006 6:44 PM