How does SQL Server control which users get automatically added to a new database?

Question

I re-create my Dev Database with a Visual Studio DB Project twice a week.

Today I fixed my permissions to be Active Directory group based rather than giving individuals permissions.

After I re-ran my build I noticed that one domain/windows use was still in there. I searched all through the DB project and could not find this user in there anywhere.

So to test, I created a different database (ie MyDummyDb). Sure enough the user was added to that db too.

This user does not even have a valid login (I changed the logins to be AD Group based too).

So I have to assume there is a server level setting that controls which users get automatically added to a new database.

Is this right? Does anyone know where this setting is?

Answer 1

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