Cross Server SQL Views Security Concern

Posted on 2009-02-10
Last Modified: 2012-05-06
I have two database servers at a data center. One for test and one for production.
I would like to create a view in the staging server that looks at a table in the production server.
I don't want any of the tables in the production server modifed by users on the staging server.
What are the security risks with having a "View" in one db access a table on another server?
It would seem to me the name view inplies only read capabilities.
So really what I'm asking is : Is it safe to do that and how can it be done since it is on a differnet server?


Question by:FastEddie___

    Author Comment

    added correct section "MS SQL Server"
    LVL 51

    Accepted Solution

    Hi Fasteddie,

    You do not mention which version of SQL Server...

    You do need to create a linked server, and part of that is establishing a secured connection by nominating the user that the remote server will recognise. You can create a fairly tightly controlled user identity on that remote server and so the linked server itself has limited capability.

    Generally speaking views are regarded as "read only", but strictly speaking, they can be used to update data if they are not calculated / computed columns, groups etc.

    To be "safe" you can add the keyword DISTINCT and any column which is part of the DISTINCT cannot be updated, or, create a FUNCTION to present back the data.

    e.g. (using examples from a MS business Contact manager instance)

    -- set up a linked server - using code - or - can use SSMS. Normally a once off task, or on startup of "this" server
    EXEC sp_addlinkedserver   
       @catalog='MSBusinessContactManager'                -- optional says what the default database will be.
    -- set up a login user to access that remote server (once off as above) - must exist on remote server and can control most security using this user
    EXEC sp_addlinkedsrvlogin @rmtsrvname = 'My_Linked_SqlSvr' 
         ,  @useself = 'FALSE' 
         ,  @locallogin =  NULL  
         ,  @rmtuser =  'My_User'             
         ,  @rmtpassword =  'My_Password'  
    -- optional, but a reasonable test and a handy piece of code to look at the linked server
    exec sp_tables_ex My_Linked_SqlSvr           -- lists tables / databases in linked server
    -- now can use the four part identifier of <server>.<database>.<schema>.<table> to access a table
    select distinct * from My_Linked_SqlSvr.MSBusinessContactManager.dbo.ContactMainTable
    -- let's now create a view on that 
    create view vw_myview_1
    select distinct * from My_Linked_SqlSvr.MSBusinessContactManager.dbo.ContactMainTable
    -- and select data from that view
    select * from vw_myview_1
    -- or create a function to retrieve selected data from that datasource and can even use this to mask the real columns and tables
    create function udf_myfunction ()
    returns @tbl table (id int, col1 varchar(60), col2 varchar(60), col3 varchar(60))
       insert @tbl 
       select contactserviceid,modifiedby,createdby,entryguid from My_Linked_SqlSvr.MSBusinessContactManager.dbo.ContactMainTable
    -- and we can select or join to that function just like a table
    select * from dbo.udf_myfunction()
    -- or even create a view over that function
    create view vw_myview_2
    select * from dbo.udf_myfunction()
    -- and use it exactly as we would any view
    select * from vw_myview_2
    -- and for the puposes of testing, can now get rid of this linked server - or leave it there for next time.
    exec sp_dropserver my_linked_SQLSvr, 'droplogins'

    Open in new window


    Author Closing Comment

    Thank you Mark. This is most usefull. I'm glad to find out that it's possible to provide limited and secure access to the production data.
    LVL 51

    Expert Comment

    by:Mark Wills
    Glad you found it usefull, but obviously something was still missing. How could I have better answered this question to get an "A" rather than a "B", or, what was missing ?

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
    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…
    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…
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

    746 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

    16 Experts available now in Live!

    Get 1:1 Help Now