Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

1 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.

share|improve this answer
    
Did that behavior change from SQL 2000 to 2005? (I never remember having that problem in the past.) Also... why does Test #1 show "table exists... but contains 0 records"? –  Jackie Aug 20 '10 at 15:20

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.