Why are there already some Users when first creating a database? (SQL Server 2017, SSMS 17.9)












0














With Microsoft SQL Server Management Studio, either executing SQL Commands (e.g. CREATE DATABASE XYZ) or through GUI to create a fresh-new database, the database ends up with some default Users (see picture below), e.g. dbo, current domain user, guest, sys.



Is this the default behavior of SQL Server? Is there anyway to control this behavior? (e.g. not including the current domain user, who is using the computer with SSMS; or having to include some users)



Another strange thing is in the list of users, one can see tester222, which I created for testing long time ago, and this name is no longer there in "SQL Server InstanceSecurityLogins" in Object Explorer. How could it just now surface in the list?



Sample Database










share|improve this question




















  • 1




    Any database created copies the template of the model database; so all those users will be in there. The dbo user is the database owner, sys and public are both part of the set up as well (sys is where all the system object lie).
    – Larnu
    Nov 22 '18 at 17:07










  • Users != Logins. Logins are at the server level (ServerSecurityLogins), users are at the database level (Databases<DB>SecurityUsers). Users are tied to a specific login. You can delete a login, and not the user; so you'd see the user in the database, but it would be orphaned and not belonging to a login.
    – Dave Cullum
    Nov 22 '18 at 18:13
















0














With Microsoft SQL Server Management Studio, either executing SQL Commands (e.g. CREATE DATABASE XYZ) or through GUI to create a fresh-new database, the database ends up with some default Users (see picture below), e.g. dbo, current domain user, guest, sys.



Is this the default behavior of SQL Server? Is there anyway to control this behavior? (e.g. not including the current domain user, who is using the computer with SSMS; or having to include some users)



Another strange thing is in the list of users, one can see tester222, which I created for testing long time ago, and this name is no longer there in "SQL Server InstanceSecurityLogins" in Object Explorer. How could it just now surface in the list?



Sample Database










share|improve this question




















  • 1




    Any database created copies the template of the model database; so all those users will be in there. The dbo user is the database owner, sys and public are both part of the set up as well (sys is where all the system object lie).
    – Larnu
    Nov 22 '18 at 17:07










  • Users != Logins. Logins are at the server level (ServerSecurityLogins), users are at the database level (Databases<DB>SecurityUsers). Users are tied to a specific login. You can delete a login, and not the user; so you'd see the user in the database, but it would be orphaned and not belonging to a login.
    – Dave Cullum
    Nov 22 '18 at 18:13














0












0








0







With Microsoft SQL Server Management Studio, either executing SQL Commands (e.g. CREATE DATABASE XYZ) or through GUI to create a fresh-new database, the database ends up with some default Users (see picture below), e.g. dbo, current domain user, guest, sys.



Is this the default behavior of SQL Server? Is there anyway to control this behavior? (e.g. not including the current domain user, who is using the computer with SSMS; or having to include some users)



Another strange thing is in the list of users, one can see tester222, which I created for testing long time ago, and this name is no longer there in "SQL Server InstanceSecurityLogins" in Object Explorer. How could it just now surface in the list?



Sample Database










share|improve this question















With Microsoft SQL Server Management Studio, either executing SQL Commands (e.g. CREATE DATABASE XYZ) or through GUI to create a fresh-new database, the database ends up with some default Users (see picture below), e.g. dbo, current domain user, guest, sys.



Is this the default behavior of SQL Server? Is there anyway to control this behavior? (e.g. not including the current domain user, who is using the computer with SSMS; or having to include some users)



Another strange thing is in the list of users, one can see tester222, which I created for testing long time ago, and this name is no longer there in "SQL Server InstanceSecurityLogins" in Object Explorer. How could it just now surface in the list?



Sample Database







sql-server database login smss






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 22 '18 at 18:48









Larnu

16.2k41630




16.2k41630










asked Nov 22 '18 at 17:03









JoyfulPandaJoyfulPanda

71




71








  • 1




    Any database created copies the template of the model database; so all those users will be in there. The dbo user is the database owner, sys and public are both part of the set up as well (sys is where all the system object lie).
    – Larnu
    Nov 22 '18 at 17:07










  • Users != Logins. Logins are at the server level (ServerSecurityLogins), users are at the database level (Databases<DB>SecurityUsers). Users are tied to a specific login. You can delete a login, and not the user; so you'd see the user in the database, but it would be orphaned and not belonging to a login.
    – Dave Cullum
    Nov 22 '18 at 18:13














  • 1




    Any database created copies the template of the model database; so all those users will be in there. The dbo user is the database owner, sys and public are both part of the set up as well (sys is where all the system object lie).
    – Larnu
    Nov 22 '18 at 17:07










  • Users != Logins. Logins are at the server level (ServerSecurityLogins), users are at the database level (Databases<DB>SecurityUsers). Users are tied to a specific login. You can delete a login, and not the user; so you'd see the user in the database, but it would be orphaned and not belonging to a login.
    – Dave Cullum
    Nov 22 '18 at 18:13








1




1




Any database created copies the template of the model database; so all those users will be in there. The dbo user is the database owner, sys and public are both part of the set up as well (sys is where all the system object lie).
– Larnu
Nov 22 '18 at 17:07




Any database created copies the template of the model database; so all those users will be in there. The dbo user is the database owner, sys and public are both part of the set up as well (sys is where all the system object lie).
– Larnu
Nov 22 '18 at 17:07












Users != Logins. Logins are at the server level (ServerSecurityLogins), users are at the database level (Databases<DB>SecurityUsers). Users are tied to a specific login. You can delete a login, and not the user; so you'd see the user in the database, but it would be orphaned and not belonging to a login.
– Dave Cullum
Nov 22 '18 at 18:13




Users != Logins. Logins are at the server level (ServerSecurityLogins), users are at the database level (Databases<DB>SecurityUsers). Users are tied to a specific login. You can delete a login, and not the user; so you'd see the user in the database, but it would be orphaned and not belonging to a login.
– Dave Cullum
Nov 22 '18 at 18:13












1 Answer
1






active

oldest

votes


















0














Thanks to @Larnu.



New databases are created based the model database. See example below:



Model Database in SQL Server 2017



If changing, say, the list of default users in this model database, every new database when created will inherit that list of user.



From Microsoft:



https://docs.microsoft.com/en-us/sql/relational-databases/databases/model-database?view=sql-server-2017




The model database is used as the template for all databases created
on an instance of SQL Server. Because tempdb is created every time SQL
Server is started, the model database must always exist on a SQL
Server system. The entire contents of the model database, including
database options, are copied to the new database. Some of the settings
of model are also used for creating a new tempdb during start up, so
the model database must always exist on a SQL Server system.







share|improve this answer





















    Your Answer






    StackExchange.ifUsing("editor", function () {
    StackExchange.using("externalEditor", function () {
    StackExchange.using("snippets", function () {
    StackExchange.snippets.init();
    });
    });
    }, "code-snippets");

    StackExchange.ready(function() {
    var channelOptions = {
    tags: "".split(" "),
    id: "1"
    };
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function() {
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled) {
    StackExchange.using("snippets", function() {
    createEditor();
    });
    }
    else {
    createEditor();
    }
    });

    function createEditor() {
    StackExchange.prepareEditor({
    heartbeatType: 'answer',
    autoActivateHeartbeat: false,
    convertImagesToLinks: true,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: 10,
    bindNavPrevention: true,
    postfix: "",
    imageUploader: {
    brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
    contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
    allowUrls: true
    },
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    });


    }
    });














    draft saved

    draft discarded


















    StackExchange.ready(
    function () {
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53435549%2fwhy-are-there-already-some-users-when-first-creating-a-database-sql-server-201%23new-answer', 'question_page');
    }
    );

    Post as a guest















    Required, but never shown

























    1 Answer
    1






    active

    oldest

    votes








    1 Answer
    1






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes









    0














    Thanks to @Larnu.



    New databases are created based the model database. See example below:



    Model Database in SQL Server 2017



    If changing, say, the list of default users in this model database, every new database when created will inherit that list of user.



    From Microsoft:



    https://docs.microsoft.com/en-us/sql/relational-databases/databases/model-database?view=sql-server-2017




    The model database is used as the template for all databases created
    on an instance of SQL Server. Because tempdb is created every time SQL
    Server is started, the model database must always exist on a SQL
    Server system. The entire contents of the model database, including
    database options, are copied to the new database. Some of the settings
    of model are also used for creating a new tempdb during start up, so
    the model database must always exist on a SQL Server system.







    share|improve this answer


























      0














      Thanks to @Larnu.



      New databases are created based the model database. See example below:



      Model Database in SQL Server 2017



      If changing, say, the list of default users in this model database, every new database when created will inherit that list of user.



      From Microsoft:



      https://docs.microsoft.com/en-us/sql/relational-databases/databases/model-database?view=sql-server-2017




      The model database is used as the template for all databases created
      on an instance of SQL Server. Because tempdb is created every time SQL
      Server is started, the model database must always exist on a SQL
      Server system. The entire contents of the model database, including
      database options, are copied to the new database. Some of the settings
      of model are also used for creating a new tempdb during start up, so
      the model database must always exist on a SQL Server system.







      share|improve this answer
























        0












        0








        0






        Thanks to @Larnu.



        New databases are created based the model database. See example below:



        Model Database in SQL Server 2017



        If changing, say, the list of default users in this model database, every new database when created will inherit that list of user.



        From Microsoft:



        https://docs.microsoft.com/en-us/sql/relational-databases/databases/model-database?view=sql-server-2017




        The model database is used as the template for all databases created
        on an instance of SQL Server. Because tempdb is created every time SQL
        Server is started, the model database must always exist on a SQL
        Server system. The entire contents of the model database, including
        database options, are copied to the new database. Some of the settings
        of model are also used for creating a new tempdb during start up, so
        the model database must always exist on a SQL Server system.







        share|improve this answer












        Thanks to @Larnu.



        New databases are created based the model database. See example below:



        Model Database in SQL Server 2017



        If changing, say, the list of default users in this model database, every new database when created will inherit that list of user.



        From Microsoft:



        https://docs.microsoft.com/en-us/sql/relational-databases/databases/model-database?view=sql-server-2017




        The model database is used as the template for all databases created
        on an instance of SQL Server. Because tempdb is created every time SQL
        Server is started, the model database must always exist on a SQL
        Server system. The entire contents of the model database, including
        database options, are copied to the new database. Some of the settings
        of model are also used for creating a new tempdb during start up, so
        the model database must always exist on a SQL Server system.








        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered Nov 23 '18 at 9:58









        JoyfulPandaJoyfulPanda

        71




        71






























            draft saved

            draft discarded




















































            Thanks for contributing an answer to Stack Overflow!


            • Please be sure to answer the question. Provide details and share your research!

            But avoid



            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.


            To learn more, see our tips on writing great answers.




            draft saved


            draft discarded














            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53435549%2fwhy-are-there-already-some-users-when-first-creating-a-database-sql-server-201%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown





















































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown

































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown







            Popular posts from this blog

            Berounka

            Sphinx de Gizeh

            Different font size/position of beamer's navigation symbols template's content depending on regular/plain...