[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 278
  • Last Modified:

SQL Database Connection question

Hi

I have inherited a website designed a long time ago which has a SQL database back end.

I have exported the database from the hosting site and I am now trying to connect to it using the existing code.


The connection to the live server is Data Source=mssql1.servername.com; Initial Catalog=Databasename;  User Id=myuser;   Password=mypassword;

I want to set the connection to my local server.

Data Source=localhost; Initial Catalog=Databasename; Integrated Security = true;

This connects to the database.

However when I run an sql query in the code

"SELECT * FROM tblA" I get an error "Invalid object name 'tblA'."

When I run the query in MSSMS it does not run either.
When I run the query "SELECT * FROM myuser.tblA" it works.

So it has something to do with the logins, but I cannot get it to work. I have tried creating a security login in MSSMS but I cannot attach it to the database.

Instead of the table name showing as dbo.tblA it shows as myuser.tblA.

Can anyone help please. It is relatively urgent as I have to do some work on this website.
0
elmbrook
Asked:
elmbrook
  • 3
1 Solution
 
Kyle AbrahamsSenior .Net DeveloperCommented:
just to get you up and running you can always connect as SA to your local, and that should grant you access to all DBs / tables.

You may need to transfer the server logins assuming you have SA on the databse there.  See the link for how to transfer server users as well as DB users:
http://www.experts-exchange.com/Q_28355004.html
0
 
elmbrookAuthor Commented:
I have access to the tables and the DB.

This web application was done using SQL commands. It's the SQL commands that do not work on the local machine.

SELECT * FROM tblA (code from the project) does not work.

SELECT * FROM myuser.tblA does work. It just means that I have to change every SQL statement in the project to do my stuff, then back again when I have made my changes which is not what I want to do.

I have just tried the script in the article but my hosting is in a shared hosting environment I do not have access to the Master database
0
 
elmbrookAuthor Commented:
I have generated scripts for the relevant tables and created a new database.
0
 
Dennis AriesCEO @ Arkro ITCommented:
Usually, when you create a database, the tables belong to 'dbo' (databaseowner). The way you describe it, the database is available to you, but it is not yours as a user. Check the owner of the database and change it if needed.
0
 
elmbrookAuthor Commented:
found solution myself by generating scripts to populate a newly created database.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now