Solved

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

Posted on 2012-04-10
5
177 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
  • 3
  • 2
5 Comments
 
LVL 15

Accepted Solution

by:
deepakChauhan 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:deepakChauhan
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:deepakChauhan
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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
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.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

808 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