Two Database Connection 1

[Auguy]

Don't know if this is the correct forum, but here goes. I have developed a .net project that can select from two SQL databases (DB1 and DB2) on my development computer using stored procedures in DB1. My connection string is setup for access to DB1. The client's DB2 might be on a different server or possibly the same server as DB1 (to be determined). I'm a little confused as to how I need to specify the connection to DB1 & DB2. On their server DB2 will be very secure, but if needed I can create a read only user for DB2. I've seen some info on linked servers, but don't know if that is the way to go. Does that open up more securiy concerns? Looking for some guidance. Thanks.

Auguy
Northwest Ohio

 

[gmmastros]

I would suggest this....

Assuming you are using SQL2005 or higher, you can create a synonym within the database. If DB1 and DB2 are on the same server, then the synonym would simply point to the other database. If DB1 and DB2 are on different servers, then the synonym would point to a linked server and database.

If you look up the "Create Synonym" command in Books On Line, you'll see that they show 2 examples, one that uses a different database on the same server, and one that uses a database on a linked server.

http://technet.microsoft.com/en-us/library/ms177544.aspx

Synonyms were first introduced in SQL2005

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Auguy]

Thanks George. Forgot to specify this is SQL 2005. Looks like that link will be very helpfull.

Auguy
Northwest Ohio

 

[Auguy]

OK, still a little confused here. All of my stored procedures are in DB1. I'm not sure how or where to set up the permissions so the stored procedures in DB1 have access to DB2. I need read only permission on DB2 through the stored procs. The users will need read/write on DB1 using the stored procs. I want to make sure nobody can change anything on DB2. Should I set up a generic user in DB1 that has permission to execute all of the stored procedures in DB1, and then put that user in the connection string for DB1? Also setting up the same user in DB2 for read only access? As you can see I need some help? As of right now these databases will be on the same server.

Auguy
Northwest Ohio