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.
but when executed with other account, my user is not listed.
- does this user have enough permissions ?