An implicit user creation has failed.

Introduction

SQL Server, in some situations automatically creates a user in a database. When that implicit user creation fails, you will get an error that might catch you off guard.

Implicit User Creation Failure

For this example, we need to first create an orphan user and a login with the same name:

This creates a login and an associated user. Right after the user is created, the login is dropped and recreated. With that, we now have a login and a user with the same name that are not associated with each other.

Now we can grant CONTROL SERVER to TestLogin1 and then have TestLogin1 create a schema. As we have seen in CONTROL SERVER vs. sysadmin - Differences when Creating a Schema, this will cause SQL Server to try to create a new user for the login, as no associated user exists. However, that attempt fails, as there is already a user with that name:

The exact error message is:

This error message you can encounter in several situations. Usually it happens when you least expect it. The most likely cause is that SQL Server is trying to implicitly create a user but fails because the user name is in use already.

Summary

In a few situations, SQL Server is creating users automatically. Usually that action happens unnoticed. However, when it fails it causes an error to be raised. That error is usually surprising. However, if you know what causes it, it is easy to fix.