Solved

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

Posted on 2012-04-10
5
179 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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Suggested Solutions

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

732 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