Assigning default schemas to users in Fabric Warehouse

[fresantos]

You can now assign default schema to your users on Fabric Data Warehouse and to Analytics SQL Endpoints. This means you can now combine the ability to create schemas on Fabric Lakehouse and benefit from this enhancement for SQL Endpoints,.

A default schema defines how database objects—like tables, views, and procedures—are organized and associated with users or groups. By default, this schema is often set to dbo, but the ability to assign custom default schemas gives users a tailored database context when accessing or managing data.

Modifying default schemas enables customers to:

• Improve performance: a well-defined schema reduces query complexity and optimizes object resolution.
• Enhance security: assigning schemas to specific users or roles minimizes unauthorized access to sensitive data.
• Organize better: logical grouping of objects by roles or projects improves manageability.
• Increase customization: tailored schemas allow users to work in contexts aligned with their tasks, reducing errors and conflicts.

This feature allows users with control plane 'write' permissions (Admin, Member, Contributor) assign default schemas to users via the ALTER USER statement. Here’s a quick example of how to use it:

ALTER USER [username] WITH DEFAULT_SCHEMA = [schema_name];

Please note that in Fabric Warehouse and SQL Endpoint, this command is focused on schema assignment only.

Users who can assign default schema (Admin, Member, Contributor) can also check the default schemas of a user using DMV’s:

SELECT name, default_schema_name
FROM sys.database_principals
WHERE type = 'S' and name = <user name>

Conclusion

The ability to assign default schemas in Fabric Data Warehouse is a major step forward for database management and security. By leveraging the ALTER USER statement, customers can create more secure, organized, and efficient environments for all users.

For more details, check the official documentation:

ALTER USER (Transact-SQL)