Solved

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

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS SQL 2005 Srink database in chunks 4 65
Where clause in stored procedure 8 60
SQL query with cast 38 51
why sql server only update some statistics in the database ? 3 32
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 I will describe the Copy Database Wizard 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.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

840 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