Privileges on SQL Server
I come from Oracle Database and now I am trying learn SQL Server and I don't undestand why may I viewer others databases, like the master for example, with a common user without privileges in this database?
sql-server permissions users logins
edited 5 hours ago
Dan Guzman
13.7k21735
asked 6 hours ago
Danilo NetoDanilo Neto
1084
New contributor
Danilo Neto is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
I come from Oracle Database and now I am trying learn SQL Server and I don't undestand why may I viewer others databases, like the master for example, with a common user without privileges in this database?
sql-server permissions users logins
edited 5 hours ago
Dan Guzman
13.7k21735
asked 6 hours ago
Danilo NetoDanilo Neto
1084
New contributor
Danilo Neto is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
I come from Oracle Database and now I am trying learn SQL Server and I don't undestand why may I viewer others databases, like the master for example, with a common user without privileges in this database?
sql-server permissions users logins
edited 5 hours ago
Dan Guzman
13.7k21735
asked 6 hours ago
Danilo NetoDanilo Neto
1084
New contributor
Danilo Neto is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
I come from Oracle Database and now I am trying learn SQL Server and I don't undestand why may I viewer others databases, like the master for example, with a common user without privileges in this database?
sql-server permissions users logins
sql-server permissions users logins
edited 5 hours ago
Dan Guzman
13.7k21735
asked 6 hours ago
Danilo NetoDanilo Neto
1084
New contributor
Danilo Neto is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited 5 hours ago
Dan Guzman
13.7k21735
asked 6 hours ago
Danilo NetoDanilo Neto
1084
New contributor
Danilo Neto is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited 5 hours ago
Dan Guzman
13.7k21735
edited 5 hours ago
Dan Guzman
13.7k21735
edited 5 hours ago
Dan Guzman
13.7k21735
13.7k21735
asked 6 hours ago
Danilo NetoDanilo Neto
1084
New contributor
Danilo Neto is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 6 hours ago
Danilo NetoDanilo Neto
1084
asked 6 hours ago
Danilo NetoDanilo Neto
1084
1084
New contributor
Danilo Neto is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
Danilo Neto is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Danilo Neto is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
You mean that you can see that the database exist, even if you don't have access to it? Yes, that is the way it is, quite simply.
From a technical standpoint, the reason is (likely) that whether or not you have access to a database is stored inside the database. So, SQL server would have to open all databases in tools such as SSMS/Object Explorer in order to determine whether or not to show each database. I.e., the info isn't stored in master.
From a practical standpoint, the reason is it is because MS did it that way. I'm not trying to be a smart-Alec here, but some things are just the way they are...
Two ways with which you can possibly handle this:
Use contained databases. If you turn on partial containment for a database, then you can login directly to the database without a login. The session will be sandboxed to that database.
Deny the VIEW ANY DATABASE server permission for the logins in question. They will no only see the databases that they own. I.e., this is only usable for persons who own the databases they will use. A non-owner will still be able to use the database it has access to, but it won't show up in sys.databases.
answered 6 hours ago
Tibor KarasziTibor Karaszi
1,7586
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
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: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
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
});
}
});
Danilo Neto is a new contributor. Be nice, and check out our Code of Conduct.
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%2fdba.stackexchange.com%2fquestions%2f229979%2fprivileges-on-sql-server%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
You mean that you can see that the database exist, even if you don't have access to it? Yes, that is the way it is, quite simply.
From a technical standpoint, the reason is (likely) that whether or not you have access to a database is stored inside the database. So, SQL server would have to open all databases in tools such as SSMS/Object Explorer in order to determine whether or not to show each database. I.e., the info isn't stored in master.
From a practical standpoint, the reason is it is because MS did it that way. I'm not trying to be a smart-Alec here, but some things are just the way they are...
Two ways with which you can possibly handle this:
Use contained databases. If you turn on partial containment for a database, then you can login directly to the database without a login. The session will be sandboxed to that database.
Deny the VIEW ANY DATABASE server permission for the logins in question. They will no only see the databases that they own. I.e., this is only usable for persons who own the databases they will use. A non-owner will still be able to use the database it has access to, but it won't show up in sys.databases.
answered 6 hours ago
Tibor KarasziTibor Karaszi
1,7586
add a comment |
You mean that you can see that the database exist, even if you don't have access to it? Yes, that is the way it is, quite simply.
From a technical standpoint, the reason is (likely) that whether or not you have access to a database is stored inside the database. So, SQL server would have to open all databases in tools such as SSMS/Object Explorer in order to determine whether or not to show each database. I.e., the info isn't stored in master.
From a practical standpoint, the reason is it is because MS did it that way. I'm not trying to be a smart-Alec here, but some things are just the way they are...
Two ways with which you can possibly handle this:
Use contained databases. If you turn on partial containment for a database, then you can login directly to the database without a login. The session will be sandboxed to that database.
Deny the VIEW ANY DATABASE server permission for the logins in question. They will no only see the databases that they own. I.e., this is only usable for persons who own the databases they will use. A non-owner will still be able to use the database it has access to, but it won't show up in sys.databases.
answered 6 hours ago
Tibor KarasziTibor Karaszi
1,7586
add a comment |
You mean that you can see that the database exist, even if you don't have access to it? Yes, that is the way it is, quite simply.
From a technical standpoint, the reason is (likely) that whether or not you have access to a database is stored inside the database. So, SQL server would have to open all databases in tools such as SSMS/Object Explorer in order to determine whether or not to show each database. I.e., the info isn't stored in master.
From a practical standpoint, the reason is it is because MS did it that way. I'm not trying to be a smart-Alec here, but some things are just the way they are...
Two ways with which you can possibly handle this:
Use contained databases. If you turn on partial containment for a database, then you can login directly to the database without a login. The session will be sandboxed to that database.
Deny the VIEW ANY DATABASE server permission for the logins in question. They will no only see the databases that they own. I.e., this is only usable for persons who own the databases they will use. A non-owner will still be able to use the database it has access to, but it won't show up in sys.databases.
answered 6 hours ago
Tibor KarasziTibor Karaszi
1,7586
You mean that you can see that the database exist, even if you don't have access to it? Yes, that is the way it is, quite simply.
From a technical standpoint, the reason is (likely) that whether or not you have access to a database is stored inside the database. So, SQL server would have to open all databases in tools such as SSMS/Object Explorer in order to determine whether or not to show each database. I.e., the info isn't stored in master.
From a practical standpoint, the reason is it is because MS did it that way. I'm not trying to be a smart-Alec here, but some things are just the way they are...
Two ways with which you can possibly handle this:
Use contained databases. If you turn on partial containment for a database, then you can login directly to the database without a login. The session will be sandboxed to that database.
Deny the VIEW ANY DATABASE server permission for the logins in question. They will no only see the databases that they own. I.e., this is only usable for persons who own the databases they will use. A non-owner will still be able to use the database it has access to, but it won't show up in sys.databases.
answered 6 hours ago
Tibor KarasziTibor Karaszi
1,7586
answered 6 hours ago
Tibor KarasziTibor Karaszi
1,7586
answered 6 hours ago
Tibor KarasziTibor Karaszi
1,7586
answered 6 hours ago
Tibor KarasziTibor Karaszi
1,7586
1,7586
add a comment |
add a comment |
Danilo Neto is a new contributor. Be nice, and check out our Code of Conduct.
Danilo Neto is a new contributor. Be nice, and check out our Code of Conduct.
Danilo Neto is a new contributor. Be nice, and check out our Code of Conduct.
Danilo Neto is a new contributor. Be nice, and check out our Code of Conduct.
Thanks for contributing an answer to Database Administrators Stack Exchange!
- 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%2fdba.stackexchange.com%2fquestions%2f229979%2fprivileges-on-sql-server%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
