Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2012-04-10
5
Medium Priority
?
183 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 16

Accepted Solution

by:
Deepak Chauhan earned 2000 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 16

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 16

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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

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 I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Screencast - Getting to Know the Pipeline
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

927 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