• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 411
  • Last Modified:

How to share data across 2 sql servers

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.
0
spanout
Asked:
spanout
1 Solution
 
HuaMinChenBusiness AnalystCommented:
Based on your requirements, I think you do not need a linked server. Instead of that you can have different databases roles for controlling the data access like what you need. See these:

Roles are database-level securables. After you create a role, configure the database-level permissions of the role by using GRANT, DENY, and REVOKE. To add members to a database role, use ALTER ROLE (Transact-SQL). For more information, see Database-Level Roles.

http://msdn.microsoft.com/en-us/library/ms187936.aspx
http://www.sqlserver2005tutorial.com/Tutorial-Learn-How-to-Create%20Roles.html
0
 
ZberteocCommented:
I agree, only to grant access to an admin you don't need another server. You can have an admin that will be granted the permission to select directly from tables.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now