• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 415
  • 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
 
HuaMin ChenSystem 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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