2

As mentioned in the subject, a new user and schema are being created when a developer I'm working with creates a stored procedure. Originally I made a user and login for that developer individually but since deleted both. However, whenever they run a script to create a SP a user and schema named after their Active Directory name are recreated and the SP is placed in that "new" schema.

Even when I delete the SP, the user, and the schema, if they create the SP again the user and schema come back.

Does anyone have any insight into this?

Improve this question

1 Answer 1

Reset to default
5

This behavior is noted in the "Remarks" section of the CREATE SCHEMA documentation, under "Implicit Schema and User Creation" (emphasis added to highlight what you are experiencing):

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.

Improve this answer
2
  • 1
    That's exactly what I needed. Thank you!
    – S W
    Dec 3, 2019 at 17:47
  • @SW You're quite welcome :)
    – Solomon Rutzky
    Dec 3, 2019 at 17:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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