XXX Schema default

Question

In Microsoft SQL Server, I have a schema XXX. Q: How do I create a user XXX such that issuing the following command:

SELECT * FROM Table

is the same as

SELECT * FROM XXX.Table

Here's what I have so far:

CREATE SCHEMA XXX authorization dbo -- I think
CREATE LOGIN XXX
WITH PASSWORD = '123';
CREATE USER ItDontMatter FOR LOGIN XXX
WITH DEFAULT_SCHEMA = XXX;

Answer 1

Correct.

The DEFAULT_SCHEMA option is what you use

Specifies the first schema that will be searched by the server when it resolves the names of objects for this database user.

and

If DEFAULT_SCHEMA is left undefined, the database user will use dbo as its default schema. DEFAULT_SCHEMA can be set to a schema that does not currently exist in the database. DEFAULT_SCHEMA can be set before the schema that it points to is created.

Answer 2

As a small note to gbn's answer, the two are never exactly the same. If you do not specify the table owner, SQL Server will not cache your query because it's not sure about access rights. So for performance, always specify schema.table in your queries, procedures, functions and views.