Avoiding SQL Server 2005 tables with same names, but with different owners

Question

What is the most likely cause of this behavor with SQL Server 2005?

TEST #1:

I run Management Studio Express, connect to my local database with "windows authentication" and see that I have 1 database, and 1 table called dbo.MyTable.

Running "SELECT * FROM MyTable" (and dbo.MyTable) shows it DOES exist, but is empty.

---

TEST #2:

I re-run Management Studio Express, connect to my 1 database with "sql authentication" myLogin/myPassword and see that I have a dbo.MyTable.

Running "SELECT * FROM MyTable" (and dbo.MyTable) gives:

Msg 229, Level 14, State 5, Line 2 
The SELECT permission was denied on the object 'MyTable', database 'master', schema 'dbo'.

---

TEST #3:

Running some common vb.net code, using my SQL authentication myLogin/myPassword, shows that I can read/write to MyTable with no problem.... and the table contains 100s of good records.

Do I really have 2-3 different tables here? (I only need 1.) I really NEVER need to have "multiple tables, with the same name, but my different owners".

What's going on here?

Answer 1

The error you give states that your Management Studio Express connection using SQL Authentication is trying to query MyTable in the master database. I highly doubt that that's the database that you want to be using.

In Management Studio make sure you're selecting your database. You can use the drop-down menu in the toolbar or, assuming your database is called MyDatabase, you can execute a USE command and then your SELECT.

USE MyDatabase
SELECT * FROM MyTable

Your VB.NET app's connection string probably already has this set up in it. It'll be the database attribute or the initial catalog attribute, and the value would be MyDatabase.