How to share data across 2 sql servers

Posted on 2012-08-18
Last Modified: 2012-09-13
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.
Question by:spanout
    LVL 10

    Accepted Solution

    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.
    LVL 26

    Expert Comment

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
    Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now