I have a SQL database with a number of tables (assume one of them is t1 and
one of the columns in T1 is t1col)

For a reason I don't understand when I sign on to query analyizer to the
database the following sql state returns an error - something like "invalid
object t1"

select all * from t1 - this fails

However, if I include the owner name of the database (e.g.,owner1) it works.

E.g. select all * from owner1.t1

Question - why is this? This has only started happening since we restored
the sql database so I guess its something to do with permissions. The impact
is that most of the SQL transactions fail because they are in the simpler
form of "select all * from t1"

Many thanks,

Dave

Bugsy (dont.spam.me@btopenworld.commy) writes:
[quoted text, click to view]

Say that you are logged in as joe your login maps to a user with the
that name joe in the database and that t1 is owned by the user mary.
If you say "SELECT * FROM t1", SQL Server will first look if there
is a table owned by joe, and then by the special user dbo (= database
owner). If there is neither a joe.t1 nor a dbo.t1, the lookup fails.
To access mary.t1, you need to specify the owner.

What is a little confusing in SQL Server, is that you have logins and
users. Logins are server-level; you log into SQL Server with an SQL
login or by Windows Authentication as your Windows user. Users are
in the database, and it's perfectly possible that the login joe maps
to the login mary. Specifically, if the login joe owns the database
in question, his login maps to the user dbo in that database.

Now, what seems likely in your case, is that the database was not
restored on the same server, or at least there had been some changes.
So rather than joe mapping to joe, joe now maps to mary. This often
happens when you restore on different servers, because the internal
ids for the logins are different.

To find out if this applies to your situation, run this command from
Query Analyzer: "EXEC sp_helpuser". If the login and user-name columns
does not match each other, you have this situation.

The system stored procedure sp_change_users_login can be used to
remedy the situation. Details in Books Online.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at