CODEWITHSUNDEEP
sql-serversql-server-2005
Phillip Senn
Phillip Senn

Reputation: 47615

XXX Schema default

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;

Upvotes: 0

Views: 1618

Answers (2)

Andomar
Andomar

Reputation: 238096

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.

Upvotes: 2

gbn
gbn

Reputation: 432271

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.

Upvotes: 4

Related Questions