Cross Server SQL Views Security Concern

Posted on 2009-02-10
Medium Priority
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___
  • 2
  • 2

Author Comment

ID: 23605143
added correct section "MS SQL Server"
LVL 51

Accepted Solution

Mark Wills earned 1500 total points
ID: 23608702
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

ID: 31545112
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
ID: 23615173
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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

807 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