2

I have a Development SQL Server that I'm managing with Active Directory Groups, sometimes when I do my checks on the server I find that some users were "automatically" added to SQL Server Database with their schema as default...

I noticed this because one user created a view and it was created with on his own schema...

Is there any configuration parameter that is allowing this? How can this be avoided?

Thanks, Jimm

  • Are you saying that you give access to the AD group and new users in the group then have access to the database? – LowlyDBA Jun 16 '17 at 14:52
  • I'm giving access to Sales AD Group for example, and then, after a while I can see that there is 2 new users created on the database, these 2 users are inside the AD Group and the user schema has been also created and assigned to them as default. This is causing that each time the create anything is created under their schema. I don't know how these users are "automatically" created as I know they don't have rights to create new users... – J1mmy Jun 16 '17 at 15:04
  • 4
    More than likely you've granted that AD group the ability to create objects, and when the users connect and create objects they are not defining the dbo schema, so the objects get created in the user schema. – Nic Jun 16 '17 at 15:05
  • @Nic is right. You probably want to change the default schema for the windows group login. – LowlyDBA Jun 16 '17 at 15:11
  • Thanks for helping :) You are right that the users have the ability to create objects but the default schema is set to dbo. Anyway I will doublecheck. – J1mmy Jun 17 '17 at 15:51
0

This is expected / documented behavior as noted in the documentation for CREATE SCHEMA (in the Remarks section):

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.

I emphasized part of that final statement: you / they need to specify a schema name when creating objects (i.e. CREATE TABLE Sales.TableName instead of CREATE TABLE TableName). The schema name that you specify does not need to be dbo, it just needs to already exist.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.