Why are there already some Users when first creating a database? (SQL Server 2017, SSMS 17.9)
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?
sql-server database login smss edited Nov 22 '18 at 18:48
Larnu
17.5k41731
asked Nov 22 '18 at 17:03
JoyfulPandaJoyfulPanda
71
add a comment |
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?
sql-server database login smss edited Nov 22 '18 at 18:48
Larnu
17.5k41731
asked Nov 22 '18 at 17:03
JoyfulPandaJoyfulPanda
71
1
Any database created copies the template of themodeldatabase; so all those users will be in there. Thedbouser is the database owner,sysandpublicare both part of the set up as well (sysis 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
add a comment |
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?
sql-server database login smss edited Nov 22 '18 at 18:48
Larnu
17.5k41731
asked Nov 22 '18 at 17:03
JoyfulPandaJoyfulPanda
71
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?
sql-server database login smss sql-server database login smss edited Nov 22 '18 at 18:48
Larnu
17.5k41731
asked Nov 22 '18 at 17:03
JoyfulPandaJoyfulPanda
71
edited Nov 22 '18 at 18:48
Larnu
17.5k41731
asked Nov 22 '18 at 17:03
JoyfulPandaJoyfulPanda
71
edited Nov 22 '18 at 18:48
Larnu
17.5k41731
edited Nov 22 '18 at 18:48
Larnu
17.5k41731
edited Nov 22 '18 at 18:48
Larnu
17.5k41731
17.5k41731
asked Nov 22 '18 at 17:03
JoyfulPandaJoyfulPanda
71
asked Nov 22 '18 at 17:03
JoyfulPandaJoyfulPanda
71
asked Nov 22 '18 at 17:03
JoyfulPandaJoyfulPanda
71
71
1
Any database created copies the template of themodeldatabase; so all those users will be in there. Thedbouser is the database owner,sysandpublicare both part of the set up as well (sysis 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
add a comment |
1
Any database created copies the template of themodeldatabase; so all those users will be in there. Thedbouser is the database owner,sysandpublicare both part of the set up as well (sysis 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
add a comment |
1 Answer
1
active
oldest
votes
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.
answered Nov 23 '18 at 9:58
JoyfulPandaJoyfulPanda
71
add a comment |
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
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
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.
answered Nov 23 '18 at 9:58
JoyfulPandaJoyfulPanda
71
add a comment |
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.
answered Nov 23 '18 at 9:58
JoyfulPandaJoyfulPanda
71
add a comment |
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.
answered Nov 23 '18 at 9:58
JoyfulPandaJoyfulPanda
71
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.
answered Nov 23 '18 at 9:58
JoyfulPandaJoyfulPanda
71
answered Nov 23 '18 at 9:58
JoyfulPandaJoyfulPanda
71
answered Nov 23 '18 at 9:58
JoyfulPandaJoyfulPanda
71
answered Nov 23 '18 at 9:58
JoyfulPandaJoyfulPanda
71
71
add a comment |
add a comment |
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.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
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
1
Any database created copies the template of the
modeldatabase; so all those users will be in there. Thedbouser is the database owner,sysandpublicare both part of the set up as well (sysis 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