SQL Server: The sentence: WITH DEFAULT_SCHEMA=[dbo] when creating a new user in a DB, what is it for?

Question

Hello

I have a question, please.

The sentence: WITH DEFAULT_SCHEMA=[dbo] when creating a new user in a DB, what is it for? What is the sentence: WITH DEFAULT_SCHEMA=[dbo] used for?

It would be: CREATE USER [Microsoft_Entra_principal] FOR LOGIN [Microsoft Entra login] WITH DEFAULT_SCHEMA=[dbo]

Thanks!

Answer 1

It follows the standard SQL Server definitions as discussed here.

That this basically means is what schema should the DB use to match identifiers that are not fully qualified. For example given a query like SELECT * FROM MyTable then SQL needs to figure out what MyTable is. It starts by looking in the default schema (which is dbo by default). Normally this should be left at the default. If you need a specific schema then you should really use the formal name (e.g. SpecialSchema.MyTable).

I don't know of a scenario where you should probably set the default schema to something other than dbo for a user. The only use case I could think of is if you were trying to use schemas as a security boundary like Oracle does. In that case you could use schemas to separate access to data and a user would need to default to their "schema" permission. But honestly it just sounds overly complex for SQL.

Answer 2

Hi,

As there may be more than one schemas in a database, so you have the option to mention default schema for a user. Which Specifies the first schema that will be searched by the server when it resolves the names of objects for this database user.

Please look into below link to understand:

https://learn.microsoft.com/en-us/sql/t-sql/statements/create-user-transact-sql?view=sql-server-ver16#with-default_schema--schema_name