How is the user granted access to default Schema

Hi @Avyayah ,

> User has access to the database and is db_owner but is it required to assign Default Schema?

No, it is not required.

The default schema will be the first schema that will be searched by the server when it resolves the names of objects for this database user. Unless otherwise specified, the default schema will be the owner of objects created by this database user.

If the user has a default schema, that default schema will be used. If the user doesn't have a default schema, but the user is a member of a group that has a default schema, the default schema of the group will be used. If the user doesn't have a default schema, and is a member of more than one group, the default schema for the user will be that of the Windows group with the lowest principal_id and an explicitly set default schema. If no default schema can be determined for a user, the dbo schema will be used.

Please note the value of DEFAULT_SCHEMA is ignored if the user is a member of the sysadmin fixed server role. All members of the sysadmin fixed server role have a default schema of dbo.

Refer to MS document ALTER USER (Transact-SQL).

We can set the default schema of a database from SSMS UI or T-SQL.

USE yourDatabase;  
ALTER USER [yourUser] WITH DEFAULT_SCHEMA = myschema;  

We can using below T-SQL to identify the default schema for DB users.

SELECT name, type_desc, default_schema_name  
FROM sys.database_principals  
WHERE type in ('S', 'U', 'G');  

If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.