How to check the default schema of WINDOWS_GROUP? 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] &lt;&gt; '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.
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 &gt; 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.