SQL Server 2016 – How to Check Default Schema of WINDOWS_GROUP

schemasql serversql-server-2016

I am using the following query to check if the default schema is set to dbo.

SELECT DB_NAME() AS [database] 
      ,[name]
      ,[type_desc]
      ,[default_schema_name]
FROM [sys].[database_principals]
WHERE [type] IN ('U', 'G')
    --AND [default_schema_name] <> 'dbo';

If not set to dbo and someone create an object without specifying a schema identifier, a new schema is created with his/her windows credentials.

So, I wanted to write a script to check (on certain period of time) if everything is set OK (because new machines and databases are restored/created on daily basis).

The issue with the script above is that I am able to see that my user's defaults schema is not dbo, but when executed with other account, my user is not listed.

I supposed that's because of the WINDOWS_GROUP that we are all member of, so I need to check its settings, but always get NULL value for groups.

Best Answer

How to check the default schema of WINDOWS_GROUP

The default schema of a group can be checked with your script.

When this script shows NULL for a group it means that the default schema was not explicitly set for this group.

but when executed with other account, my user is not listed

This means that this account is not explicitly mapped to this database, but it's a member of one ore more Windows groups that are mapped.

The rule here is:

If the user has a default schema, that default schema will used. If the user does not 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 does not 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.

When you see that

someone create an object without specifying a schema identifier, a new schema is created with his/her windows credentials

this means that this user the user is member of one or more Windows groups and none of these group has a default schema.

If you want to prevent this "schema creation" you should not leave users-Windows groups without default schema, just assign dbo schema to dbo:

alter user [someDomain\someGroup] with default_schema = dbo

To check your user's groups mapped to this database execute the following code:

select *
from sys.user_token
where principal_id > 0

You can filter here on type = 'WINDOWS GROUP' but when non filtered, you see all the tokens, so if you don't see windows account as it is, it's because it was not explicitly mapped to this database and it reaches the database through Windows group(s)

UPDATE

This behaviour is called implicit schema creation and is documented here: CREATE SCHEMA (Transact-SQL)

Implicit Schema and User Creation

In some cases a user can use a database without having a database user account (a database principal in the database). This can happen in the following situations:

A login has CONTROL SERVER privileges.

A Windows user does not have an individual database user account (a database principal in the database), but accesses a database as a member of a Windows group which has a database user account (a database principal for the Windows group).

When a user without a database user account creates an object without specifying an existing schema, a database principal and default schema will be automatically created in the database for that user. The created database principal and schema will have the same name as the name that user used when connecting to SQL Server (the SQL Server authentication login name or the Windows user name).

This behavior is necessary to allow users that are based on Windows groups to create and own objects. However it can result in the unintentional creation of schemas and users. To avoid implicitly creating users and schemas, whenever possible explicitly create database principals and assign a default schema. Or explicitly state an existing schema when creating objects in a database, using two or three-part object names.

Related Question