Sql-server – User created automatically because of AD Group

schemasql serversql server 2014users

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

Best Answer

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.

Related Solutions

Sql-server – How does SQL Server control which users get automatically added to a new database

You have a user defined in the model database. Good odds someone mis-clicked once upon a time and created the spurious user in model accidentally. model is the template for any databases created on an instance, so if a user exists there it will exist in all databases you create.

Run the script below (on a development instance!) and you'll see a user created in model is added to the TestForModelUser database.

USE [master]
GO

CREATE LOGIN [ModelUser] WITH PASSWORD=N'', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

USE [model]
GO

CREATE USER [ModelUser] FOR LOGIN [ModelUser]
GO

CREATE DATABASE TestForModelUser
GO

USE [TestForModelUser]
GO

SELECT * FROM sys.sysusers WHERE [name] = 'ModelUser'
GO

USE [master]
GO

DROP DATABASE [TestForModelUser]
GO

USE [model]
GO

DROP USER [ModelUser]
GO

USE [master]
GO

DROP LOGIN [ModelUser]
GO

SQL Server Script – Delete Accounts Not in Active Directory

What I ended doing is listing the accounts with:

    exec sp_validatelogins

And running

    exec sp_dropuser loginname
    exec sp_droplogin loginname

on the results.

Best Answer

Related Solutions

Sql-server – How does SQL Server control which users get automatically added to a new database

SQL Server Script – Delete Accounts Not in Active Directory

Related Question