locked
Windows Authentication Login is created automatically in SQL DB RRS feed

  • Question

  • Hi

    In my DB (SQL 2012) one windows authentication is created automatically and a schema is created exactly automatically that this user is owner of this schema. (e.g the user is domain\x and schema exactly domain\x)

    I created a DDL Trigger on my DB to find out who and how it is created , but I didn't get any useful information.

    I change the owner of schema that I can delete the schema and after that delete the user, and sometimes because the user is owner of a service , I have to alter the owner of the service to DBO and delete the user , but again and again exactly this user and schema (e.g domanin\x ) are created as soon as I delete it.

    I have read these topics :

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/0c4db82a-4b93-41d1-8e3a-786eaaac550f/can-a-user-be-automatically-added-to-a-database?forum=sqlsecurity

    and 

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/f3280d73-2000-4d32-bff1-df8d138fefd2/users-automatically-added-to-new-db?forum=sqlsecurity

    but can't solve my problem.

    Dose anybody know why it's happened ? 

    Friday, April 24, 2015 7:56 PM

Answers

  • so, a domiain login is created on the server and it is being created again- even if you after dropped the login.

     can you create audit server audit-- and see how it is being created - you said, it is being right after you deleted the login

    can you create a trace/extended event and check what's creating after you drop. should be easy if it is being created right after you dropped it..

    if you do not know when it is being created - just use server audit - login created event(not excatly this).. it should tell you.

    my guess, is some third party application - what is login looklike -- like service account/third party account.. can you network team throw some light on what the login is ??


    Hope it Helps!!

    • Marked as answer by Ensy Tehrani Tuesday, April 28, 2015 9:29 PM
    Friday, April 24, 2015 8:31 PM
  • So referring the answer in the first thread you posted a link to, have you checked if there are any objects in this schema, or any objects created by this user?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Ensy Tehrani Tuesday, April 28, 2015 9:29 PM
    Friday, April 24, 2015 9:25 PM
  • In my DB (SQL 2012) one windows authentication is created automatically and a schema is created exactly automatically that this user is owner of this schema. (e.g the user is domain\x and schema exactly domain\x)

    Hello,

    New databases are created as a copy of the system database "Model", so I guess this Windows login + schema already exists in this "Model" database and therefore in all new databases; so check your "Model" database.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by Ensy Tehrani Tuesday, April 28, 2015 9:29 PM
    Saturday, April 25, 2015 6:05 AM
  • You may be experiencing implicit user and schema creation. See Books Online for CREATE SCHEMA https://msdn.microsoft.com/en-us/library/ms189462.aspx

    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.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Tuesday, April 28, 2015 4:28 PM

All replies

  • so, a domiain login is created on the server and it is being created again- even if you after dropped the login.

     can you create audit server audit-- and see how it is being created - you said, it is being right after you deleted the login

    can you create a trace/extended event and check what's creating after you drop. should be easy if it is being created right after you dropped it..

    if you do not know when it is being created - just use server audit - login created event(not excatly this).. it should tell you.

    my guess, is some third party application - what is login looklike -- like service account/third party account.. can you network team throw some light on what the login is ??


    Hope it Helps!!

    • Marked as answer by Ensy Tehrani Tuesday, April 28, 2015 9:29 PM
    Friday, April 24, 2015 8:31 PM
  • So referring the answer in the first thread you posted a link to, have you checked if there are any objects in this schema, or any objects created by this user?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Ensy Tehrani Tuesday, April 28, 2015 9:29 PM
    Friday, April 24, 2015 9:25 PM
  • In my DB (SQL 2012) one windows authentication is created automatically and a schema is created exactly automatically that this user is owner of this schema. (e.g the user is domain\x and schema exactly domain\x)

    Hello,

    New databases are created as a copy of the system database "Model", so I guess this Windows login + schema already exists in this "Model" database and therefore in all new databases; so check your "Model" database.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by Ensy Tehrani Tuesday, April 28, 2015 9:29 PM
    Saturday, April 25, 2015 6:05 AM
  • Thanks 

    but it wasn't in the MODEL db

    Monday, April 27, 2015 8:15 PM
  • Thanks

    I guess exactly like you , there should be third party application.

    as I have created a trigger on DB , it just shows me whenever a user is created or deleted within the SQL DB and doesn't show anything when this this user is created automatically 

    Monday, April 27, 2015 8:31 PM
  • This user before created some object , but the owner of the objects are DBO , not this user 
    Monday, April 27, 2015 8:32 PM
  • You may be experiencing implicit user and schema creation. See Books Online for CREATE SCHEMA https://msdn.microsoft.com/en-us/library/ms189462.aspx

    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.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Tuesday, April 28, 2015 4:28 PM