Solved

Sql 2005: Linking two databases together on the same server and having write permission on the second

Posted on 2012-04-10
5
180 Views
Last Modified: 2012-05-02
I have a sql server 2005 database and only have readonly access.(DB A)

I want to be able to create a view and write reports. However I decided to create another database where I can create a view since I cannot do that in the readonly access(DB B)

How do I link the database  (DB B) to database  (DB A)?  Is linked server the right tool? Also note that I will need to point a web application to this new database (DB B) meaning that I will need to have a connection string available to use in my asp.net code.

The two databases reside on the same server.
0
Comment
Question by:Sirdots
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 15

Accepted Solution

by:
Deepak Chauhan earned 500 total points
ID: 37829769
Sirdots , if your both database 'DB1' and DB2 are on same server then you Don't need to create a linked server. Just run this script on DB2

create view VW_1 as select col1,col2,col3 or * from DB1.dbo.tablename

or if DB1 on another server then linked sever must in this case statement will be

Create view VW_1 as select col1,col2,col3 or * from [linkedserver].DB1.dbo.tablename

 As linked server  make the database accessible of other servers.

Hope so your problem would be solve
0
 

Author Comment

by:Sirdots
ID: 37829909
Thanks  deepakChauhan. The read only database has a user Id and password. Dont you think that I will need that in my view creation like something below

Create view VW_1
as
SELECT   *
FROM      OPENDATASOURCE(
         'SQLOLEDB',
         'Data Source=ServerName;User ID=MyUID;Password=MyPass'
         ).DBA.dbo.Categories
0
 
LVL 15

Expert Comment

by:Deepak Chauhan
ID: 37830062
This is a wrong approach to show userid and pass in view creation. If i run  
select * sys.all_sql_modules  where definition like '%VW_1%'
 i got easily your id and password.

So it is best practice to create a linked server using sp_addlinked_server system stored procedure or using SSMS then use it as according to you. and your userid and password will be secured,


for throughout information about linked server please follow the link it is more helpfull
http://msdn.microsoft.com/en-us/library/ms190479.aspx
0
 

Author Comment

by:Sirdots
ID: 37830087
Now you got me confused. Initially you said I should not use linked server but now I should use linked server again.  Which one exactly is good?
0
 
LVL 15

Expert Comment

by:Deepak Chauhan
ID: 37841501
Sirdos , U can use link server to create a view but not like this. In below view definition u r storing userid and password openly in a view definition If some one queryying to system table/View as "select * from sys.all_sql_modules where definition like '%VW_1%' then your complete view script will be exposed and USERID AND PASSWORD ALSO. I suggest you please first create a linked server using link server wizard or using sp_addlinked_server system stored procedures.  If you follow this approach your password will

like this

create linked server>>> right click on the linked server folder under server object folder in sql server management studio and fill the necessary information.  like linked sever name security credentials for more and step by step information abot linked sever
follow the link.
http://msdn.microsoft.com/en-us/library/aa560998(v=bts.10).aspx
http://msdn.microsoft.com/en-us/library/ms190479.aspx

after create linked server

run this statment >>

Create view VW_1 as SELECT   * FROM   [<linkedservername>].DBA.dbo.Categories

not like this

Create view VW_1
as
SELECT   *
FROM      OPENDATASOURCE(
         'SQLOLEDB',
         'Data Source=ServerName;User ID=MyUID;Password=MyPass' >> password exposing
         ).DBA.dbo.Categories



Create view VW_1
as
SELECT   *
FROM      OPENDATASOURCE(
         'SQLOLEDB',
         'Data Source=ServerName;User ID=MyUID;Password=MyPass'
         ).DBA.dbo.Categories
0

Featured Post

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

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

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

622 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