Caveats when using Schemas in SQL Server DB
I last looked at Schemas (on Oracle) about 20 years ago, I know that Microsoft changed schemas in SQL Server 2005. We're now about to create a new application and I've long wanted to take another look at schemas.
We use 1 specific login to do the applications work so it has db_owner role and 1 specific login for running all reports so it has data_reader role.
I've done my research as well as poked around and wrote some scripts. This script "appears" to be all I need in order to create a schema:
CREATE SCHEMA [MySchema];
I used the sa user to create the schema and related tables. From there, I've been able to create tables within the schema and access them just fine from the two users.
My question is, was this very simple statement all that there is required to create a schema "correctly" and are there any specifics I should be watching out for?
We already access all db objects with the [dbo]. schema prefix in preparation for going multi-schema. I'm just not sure if there's something sneaky when we finally start getting into stored procedures, functions, views, indexes, foreign keys and the likes. So far all my testing has come up roses but I'm concerned I'm missing something that's going to really beat me with a stick some ways down the line.
sql-server database-design database-schema sql-server-2016 asked Nov 13 '18 at 5:53
StormStorm
5652518
|
show 2 more comments
I last looked at Schemas (on Oracle) about 20 years ago, I know that Microsoft changed schemas in SQL Server 2005. We're now about to create a new application and I've long wanted to take another look at schemas.
We use 1 specific login to do the applications work so it has db_owner role and 1 specific login for running all reports so it has data_reader role.
I've done my research as well as poked around and wrote some scripts. This script "appears" to be all I need in order to create a schema:
CREATE SCHEMA [MySchema];
I used the sa user to create the schema and related tables. From there, I've been able to create tables within the schema and access them just fine from the two users.
My question is, was this very simple statement all that there is required to create a schema "correctly" and are there any specifics I should be watching out for?
We already access all db objects with the [dbo]. schema prefix in preparation for going multi-schema. I'm just not sure if there's something sneaky when we finally start getting into stored procedures, functions, views, indexes, foreign keys and the likes. So far all my testing has come up roses but I'm concerned I'm missing something that's going to really beat me with a stick some ways down the line.
sql-server database-design database-schema sql-server-2016 asked Nov 13 '18 at 5:53
StormStorm
5652518
One question per post please.
– philipxy
Nov 13 '18 at 6:03
1
My only feedback is some client tools don't play nice with schemas or are not schema aware. It really depends on your client tool. Last time I checked, Entity Framework was not schema aware - if you had the same object name in two schemas it got confused.
– Nick.McDermaid
Nov 13 '18 at 6:16
All the other questions you have are basically security questions.data_readeris not a built in database role. If you meantdb_datareaderthen even then that user mightbe explicitly granted other access. You need to check the grants on the user. In short schemas are a good idea and you should use them but beforehand consider the "orthogonality" - will schemas represent business units, business processes, application modules etc.
– Nick.McDermaid
Nov 13 '18 at 6:19
@ Nick.McDermaid: you can use schemas with EF, but it's not great....
– Mitch Wheat
Nov 13 '18 at 6:45
Personally, I wouldn't use a user in role db_owner for application access, Create a user and give it data_reader/data_writer
– Mitch Wheat
Nov 13 '18 at 6:49
|
show 2 more comments
I last looked at Schemas (on Oracle) about 20 years ago, I know that Microsoft changed schemas in SQL Server 2005. We're now about to create a new application and I've long wanted to take another look at schemas.
We use 1 specific login to do the applications work so it has db_owner role and 1 specific login for running all reports so it has data_reader role.
I've done my research as well as poked around and wrote some scripts. This script "appears" to be all I need in order to create a schema:
CREATE SCHEMA [MySchema];
I used the sa user to create the schema and related tables. From there, I've been able to create tables within the schema and access them just fine from the two users.
My question is, was this very simple statement all that there is required to create a schema "correctly" and are there any specifics I should be watching out for?
We already access all db objects with the [dbo]. schema prefix in preparation for going multi-schema. I'm just not sure if there's something sneaky when we finally start getting into stored procedures, functions, views, indexes, foreign keys and the likes. So far all my testing has come up roses but I'm concerned I'm missing something that's going to really beat me with a stick some ways down the line.
sql-server database-design database-schema sql-server-2016 asked Nov 13 '18 at 5:53
StormStorm
5652518
I last looked at Schemas (on Oracle) about 20 years ago, I know that Microsoft changed schemas in SQL Server 2005. We're now about to create a new application and I've long wanted to take another look at schemas.
We use 1 specific login to do the applications work so it has db_owner role and 1 specific login for running all reports so it has data_reader role.
I've done my research as well as poked around and wrote some scripts. This script "appears" to be all I need in order to create a schema:
CREATE SCHEMA [MySchema];
I used the sa user to create the schema and related tables. From there, I've been able to create tables within the schema and access them just fine from the two users.
My question is, was this very simple statement all that there is required to create a schema "correctly" and are there any specifics I should be watching out for?
We already access all db objects with the [dbo]. schema prefix in preparation for going multi-schema. I'm just not sure if there's something sneaky when we finally start getting into stored procedures, functions, views, indexes, foreign keys and the likes. So far all my testing has come up roses but I'm concerned I'm missing something that's going to really beat me with a stick some ways down the line.
sql-server database-design database-schema sql-server-2016 sql-server database-design database-schema sql-server-2016 asked Nov 13 '18 at 5:53
StormStorm
5652518
asked Nov 13 '18 at 5:53
StormStorm
5652518
edited Nov 13 '18 at 8:08
Storm
asked Nov 13 '18 at 5:53
StormStorm
5652518
asked Nov 13 '18 at 5:53
StormStorm
5652518
asked Nov 13 '18 at 5:53
StormStorm
5652518
5652518
One question per post please.
– philipxy
Nov 13 '18 at 6:03
1
My only feedback is some client tools don't play nice with schemas or are not schema aware. It really depends on your client tool. Last time I checked, Entity Framework was not schema aware - if you had the same object name in two schemas it got confused.
– Nick.McDermaid
Nov 13 '18 at 6:16
All the other questions you have are basically security questions.data_readeris not a built in database role. If you meantdb_datareaderthen even then that user mightbe explicitly granted other access. You need to check the grants on the user. In short schemas are a good idea and you should use them but beforehand consider the "orthogonality" - will schemas represent business units, business processes, application modules etc.
– Nick.McDermaid
Nov 13 '18 at 6:19
@ Nick.McDermaid: you can use schemas with EF, but it's not great....
– Mitch Wheat
Nov 13 '18 at 6:45
Personally, I wouldn't use a user in role db_owner for application access, Create a user and give it data_reader/data_writer
– Mitch Wheat
Nov 13 '18 at 6:49
|
show 2 more comments
One question per post please.
– philipxy
Nov 13 '18 at 6:03
1
My only feedback is some client tools don't play nice with schemas or are not schema aware. It really depends on your client tool. Last time I checked, Entity Framework was not schema aware - if you had the same object name in two schemas it got confused.
– Nick.McDermaid
Nov 13 '18 at 6:16
All the other questions you have are basically security questions.data_readeris not a built in database role. If you meantdb_datareaderthen even then that user mightbe explicitly granted other access. You need to check the grants on the user. In short schemas are a good idea and you should use them but beforehand consider the "orthogonality" - will schemas represent business units, business processes, application modules etc.
– Nick.McDermaid
Nov 13 '18 at 6:19
@ Nick.McDermaid: you can use schemas with EF, but it's not great....
– Mitch Wheat
Nov 13 '18 at 6:45
Personally, I wouldn't use a user in role db_owner for application access, Create a user and give it data_reader/data_writer
– Mitch Wheat
Nov 13 '18 at 6:49
One question per post please.
– philipxy
Nov 13 '18 at 6:03
One question per post please.
– philipxy
Nov 13 '18 at 6:03
1
1
My only feedback is some client tools don't play nice with schemas or are not schema aware. It really depends on your client tool. Last time I checked, Entity Framework was not schema aware - if you had the same object name in two schemas it got confused.
– Nick.McDermaid
Nov 13 '18 at 6:16
My only feedback is some client tools don't play nice with schemas or are not schema aware. It really depends on your client tool. Last time I checked, Entity Framework was not schema aware - if you had the same object name in two schemas it got confused.
– Nick.McDermaid
Nov 13 '18 at 6:16
All the other questions you have are basically security questions.
data_reader is not a built in database role. If you meant db_datareader then even then that user mightbe explicitly granted other access. You need to check the grants on the user. In short schemas are a good idea and you should use them but beforehand consider the "orthogonality" - will schemas represent business units, business processes, application modules etc.– Nick.McDermaid
Nov 13 '18 at 6:19
All the other questions you have are basically security questions.
data_reader is not a built in database role. If you meant db_datareader then even then that user mightbe explicitly granted other access. You need to check the grants on the user. In short schemas are a good idea and you should use them but beforehand consider the "orthogonality" - will schemas represent business units, business processes, application modules etc.– Nick.McDermaid
Nov 13 '18 at 6:19
@ Nick.McDermaid: you can use schemas with EF, but it's not great....
– Mitch Wheat
Nov 13 '18 at 6:45
@ Nick.McDermaid: you can use schemas with EF, but it's not great....
– Mitch Wheat
Nov 13 '18 at 6:45
Personally, I wouldn't use a user in role db_owner for application access, Create a user and give it data_reader/data_writer
– Mitch Wheat
Nov 13 '18 at 6:49
Personally, I wouldn't use a user in role db_owner for application access, Create a user and give it data_reader/data_writer
– Mitch Wheat
Nov 13 '18 at 6:49
|
show 2 more comments
0
active
oldest
votes
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%2f53274644%2fcaveats-when-using-schemas-in-sql-server-db%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
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%2f53274644%2fcaveats-when-using-schemas-in-sql-server-db%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
One question per post please.
– philipxy
Nov 13 '18 at 6:03
1
My only feedback is some client tools don't play nice with schemas or are not schema aware. It really depends on your client tool. Last time I checked, Entity Framework was not schema aware - if you had the same object name in two schemas it got confused.
– Nick.McDermaid
Nov 13 '18 at 6:16
All the other questions you have are basically security questions.
data_readeris not a built in database role. If you meantdb_datareaderthen even then that user mightbe explicitly granted other access. You need to check the grants on the user. In short schemas are a good idea and you should use them but beforehand consider the "orthogonality" - will schemas represent business units, business processes, application modules etc.– Nick.McDermaid
Nov 13 '18 at 6:19
@ Nick.McDermaid: you can use schemas with EF, but it's not great....
– Mitch Wheat
Nov 13 '18 at 6:45
Personally, I wouldn't use a user in role db_owner for application access, Create a user and give it data_reader/data_writer
– Mitch Wheat
Nov 13 '18 at 6:49