We help IT Professionals succeed at work.

Cross Server SQL Views Security Concern

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?


Watch Question


added correct section "MS SQL Server"
Topic Advisor, Page Editor
Distinguished Expert 2018
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts


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.
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

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 ?
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.