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











up vote
0
down vote

favorite












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 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 at 18:13















up vote
0
down vote

favorite












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 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 at 18:13













up vote
0
down vote

favorite









up vote
0
down vote

favorite











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 at 18:48









Larnu

14.6k31530




14.6k31530










asked Nov 22 at 17:03









JoyfulPanda

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 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 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 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 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 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 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 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 at 18:13












1 Answer
1






active

oldest

votes

















up vote
0
down vote













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',
    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








    up vote
    0
    down vote













    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

























      up vote
      0
      down vote













      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























        up vote
        0
        down vote










        up vote
        0
        down vote









        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 at 9:58









        JoyfulPanda

        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.





            Some of your past answers have not been well-received, and you're in danger of being blocked from answering.


            Please pay close attention to the following guidance:


            • 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

            What visual should I use to simply compare current year value vs last year in Power BI desktop

            How to ignore python UserWarning in pytest?

            Alexandru Averescu