How to share data across 2 sql servers
Posted on 2012-08-18
I have an interesting situation. We are building a multi-tenented system and implementing row level security based on sql users. So a user will automatically only see data that applies to them if they were to inject a select * command into a table. To do this, we are removing select access on tables and accessing all data via views with the sql user seeded into the view 's where clause. This seems to meet requirement.
Unfortunately, we have admin who should be able to see everything at once. Having a user with this level of access is perceived a risk so we are considering taking tha admin functionality to another more secure server.
Would we use a linked SQL server to coordinate the data between the 2 apps? The admin requires full read write access on records.
Any guidance is greatly appreciated.