Find User’s Default Schema

Need to find the default schema for a user in MS SQL?  Need to confirm all your SQL Server users default schema's are set correctly?  Easy peasey.  Run the following query and enjoy.

(Not sure why you would care what the default schema is for a user?  Better performance is the answer.  You care because in a case where a schema was not specified in a query, SQL Server will first check for the existence of the object in the user's default schema.)

select default_schema_name
, type_desc
, name
, create_date
from sys.database_principals
order by default_schema_name
, type_desc
, name

By Phil Steffek

Phil Steffek is a professional sumo wrestler who is hoping to represent the U.S. in the next Olympics. During the off season Phil is a Data Architect working on transactional databases, data warehouse implementations, and all aspects of business intelligence. Also, Phil is not really a sumo wrestler. You can get to know a bit more about my non-sumo doings by checking out my LinkedIn Page

Leave a comment

Your email address will not be published. Required fields are marked *