How to drop a SQL Server user with db owner privilege
I need to drop a user with dbowner schema from a SQL Server database. I cannot drop it as it is since I get this error message
Drop failed for User 'network service'. (Microsoft.SqlServer.Smo)
The database principal owns a schema in the database, and cannot be dropped. (Microsoft SQL Server, Error: 15138)
When I try to uncheck the schema owned by this user to remove db owner it does nothing. My question is how I can drop this user or edit its name from 'network service' to 'NT AUTHORITYNETWORK SERVICE'
sql-server sql-manager edited Feb 16 '16 at 14:32
marc_s
575k12811091256
asked Feb 16 '16 at 13:50
Sofia KhwajaSofia Khwaja
4992716
add a comment |
I need to drop a user with dbowner schema from a SQL Server database. I cannot drop it as it is since I get this error message
Drop failed for User 'network service'. (Microsoft.SqlServer.Smo)
The database principal owns a schema in the database, and cannot be dropped. (Microsoft SQL Server, Error: 15138)
When I try to uncheck the schema owned by this user to remove db owner it does nothing. My question is how I can drop this user or edit its name from 'network service' to 'NT AUTHORITYNETWORK SERVICE'
sql-server sql-manager edited Feb 16 '16 at 14:32
marc_s
575k12811091256
asked Feb 16 '16 at 13:50
Sofia KhwajaSofia Khwaja
4992716
add a comment |
I need to drop a user with dbowner schema from a SQL Server database. I cannot drop it as it is since I get this error message
Drop failed for User 'network service'. (Microsoft.SqlServer.Smo)
The database principal owns a schema in the database, and cannot be dropped. (Microsoft SQL Server, Error: 15138)
When I try to uncheck the schema owned by this user to remove db owner it does nothing. My question is how I can drop this user or edit its name from 'network service' to 'NT AUTHORITYNETWORK SERVICE'
sql-server sql-manager edited Feb 16 '16 at 14:32
marc_s
575k12811091256
asked Feb 16 '16 at 13:50
Sofia KhwajaSofia Khwaja
4992716
I need to drop a user with dbowner schema from a SQL Server database. I cannot drop it as it is since I get this error message
Drop failed for User 'network service'. (Microsoft.SqlServer.Smo)
The database principal owns a schema in the database, and cannot be dropped. (Microsoft SQL Server, Error: 15138)
When I try to uncheck the schema owned by this user to remove db owner it does nothing. My question is how I can drop this user or edit its name from 'network service' to 'NT AUTHORITYNETWORK SERVICE'
sql-server sql-manager sql-server sql-manager edited Feb 16 '16 at 14:32
marc_s
575k12811091256
asked Feb 16 '16 at 13:50
Sofia KhwajaSofia Khwaja
4992716
edited Feb 16 '16 at 14:32
marc_s
575k12811091256
asked Feb 16 '16 at 13:50
Sofia KhwajaSofia Khwaja
4992716
edited Feb 16 '16 at 14:32
marc_s
575k12811091256
edited Feb 16 '16 at 14:32
marc_s
575k12811091256
edited Feb 16 '16 at 14:32
marc_s
575k12811091256
575k12811091256
asked Feb 16 '16 at 13:50
Sofia KhwajaSofia Khwaja
4992716
asked Feb 16 '16 at 13:50
Sofia KhwajaSofia Khwaja
4992716
asked Feb 16 '16 at 13:50
Sofia KhwajaSofia Khwaja
4992716
4992716
add a comment |
add a comment |
3 Answers
3
active
oldest
votes
take a look at this:
http://www.itorian.com/2012/12/the-database-principal-owns-schema-in.html
It suggests that you need to add another owner first
answered Feb 16 '16 at 14:07
Kevin MKevin M
20819
add a comment |
I had the same problem, i run two script then my problem is solved.
try this:
In this query you can get user schema as a result for AdventureWorks database
USE AdventureWorks;
SELECT s.name
FROM sys.schemas s
WHERE s.principal_id = USER_ID('your username');
after take schema name you can alter authorization on schema like this:
ALTER AUTHORIZATION ON SCHEMA::db_owner TO dbo;
in this query db_owner schema name that get from first query.
finally you can delete user without error.
my source: SQL SERVER – Fix: Error: 15138
answered May 9 '18 at 9:36
Hasan FathiHasan Fathi
2,11412026
1
This option worked for me after trying the accepted answer first (which did not help; not sure why, they are very similar).
– AcePL
Aug 15 '18 at 15:43
add a comment |
ALTER AUTHORIZATION ON SCHEMA::[NT AUTHORITYSYSTEM] TO dbo
edited Nov 25 '18 at 15:17
Larnu
17.3k41630
answered Nov 25 '18 at 13:07
Joy BhattacharyaJoy Bhattacharya
1
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%2f35434389%2fhow-to-drop-a-sql-server-user-with-db-owner-privilege%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
take a look at this:
http://www.itorian.com/2012/12/the-database-principal-owns-schema-in.html
It suggests that you need to add another owner first
answered Feb 16 '16 at 14:07
Kevin MKevin M
20819
add a comment |
take a look at this:
http://www.itorian.com/2012/12/the-database-principal-owns-schema-in.html
It suggests that you need to add another owner first
answered Feb 16 '16 at 14:07
Kevin MKevin M
20819
add a comment |
take a look at this:
http://www.itorian.com/2012/12/the-database-principal-owns-schema-in.html
It suggests that you need to add another owner first
answered Feb 16 '16 at 14:07
Kevin MKevin M
20819
take a look at this:
http://www.itorian.com/2012/12/the-database-principal-owns-schema-in.html
It suggests that you need to add another owner first
answered Feb 16 '16 at 14:07
Kevin MKevin M
20819
answered Feb 16 '16 at 14:07
Kevin MKevin M
20819
answered Feb 16 '16 at 14:07
Kevin MKevin M
20819
answered Feb 16 '16 at 14:07
Kevin MKevin M
20819
20819
add a comment |
add a comment |
I had the same problem, i run two script then my problem is solved.
try this:
In this query you can get user schema as a result for AdventureWorks database
USE AdventureWorks;
SELECT s.name
FROM sys.schemas s
WHERE s.principal_id = USER_ID('your username');
after take schema name you can alter authorization on schema like this:
ALTER AUTHORIZATION ON SCHEMA::db_owner TO dbo;
in this query db_owner schema name that get from first query.
finally you can delete user without error.
my source: SQL SERVER – Fix: Error: 15138
answered May 9 '18 at 9:36
Hasan FathiHasan Fathi
2,11412026
1
This option worked for me after trying the accepted answer first (which did not help; not sure why, they are very similar).
– AcePL
Aug 15 '18 at 15:43
add a comment |
I had the same problem, i run two script then my problem is solved.
try this:
In this query you can get user schema as a result for AdventureWorks database
USE AdventureWorks;
SELECT s.name
FROM sys.schemas s
WHERE s.principal_id = USER_ID('your username');
after take schema name you can alter authorization on schema like this:
ALTER AUTHORIZATION ON SCHEMA::db_owner TO dbo;
in this query db_owner schema name that get from first query.
finally you can delete user without error.
my source: SQL SERVER – Fix: Error: 15138
answered May 9 '18 at 9:36
Hasan FathiHasan Fathi
2,11412026
1
This option worked for me after trying the accepted answer first (which did not help; not sure why, they are very similar).
– AcePL
Aug 15 '18 at 15:43
add a comment |
I had the same problem, i run two script then my problem is solved.
try this:
In this query you can get user schema as a result for AdventureWorks database
USE AdventureWorks;
SELECT s.name
FROM sys.schemas s
WHERE s.principal_id = USER_ID('your username');
after take schema name you can alter authorization on schema like this:
ALTER AUTHORIZATION ON SCHEMA::db_owner TO dbo;
in this query db_owner schema name that get from first query.
finally you can delete user without error.
my source: SQL SERVER – Fix: Error: 15138
answered May 9 '18 at 9:36
Hasan FathiHasan Fathi
2,11412026
I had the same problem, i run two script then my problem is solved.
try this:
In this query you can get user schema as a result for AdventureWorks database
USE AdventureWorks;
SELECT s.name
FROM sys.schemas s
WHERE s.principal_id = USER_ID('your username');
after take schema name you can alter authorization on schema like this:
ALTER AUTHORIZATION ON SCHEMA::db_owner TO dbo;
in this query db_owner schema name that get from first query.
finally you can delete user without error.
my source: SQL SERVER – Fix: Error: 15138
answered May 9 '18 at 9:36
Hasan FathiHasan Fathi
2,11412026
answered May 9 '18 at 9:36
Hasan FathiHasan Fathi
2,11412026
answered May 9 '18 at 9:36
Hasan FathiHasan Fathi
2,11412026
answered May 9 '18 at 9:36
Hasan FathiHasan Fathi
2,11412026
2,11412026
1
This option worked for me after trying the accepted answer first (which did not help; not sure why, they are very similar).
– AcePL
Aug 15 '18 at 15:43
add a comment |
1
This option worked for me after trying the accepted answer first (which did not help; not sure why, they are very similar).
– AcePL
Aug 15 '18 at 15:43
1
1
This option worked for me after trying the accepted answer first (which did not help; not sure why, they are very similar).
– AcePL
Aug 15 '18 at 15:43
This option worked for me after trying the accepted answer first (which did not help; not sure why, they are very similar).
– AcePL
Aug 15 '18 at 15:43
add a comment |
ALTER AUTHORIZATION ON SCHEMA::[NT AUTHORITYSYSTEM] TO dbo
edited Nov 25 '18 at 15:17
Larnu
17.3k41630
answered Nov 25 '18 at 13:07
Joy BhattacharyaJoy Bhattacharya
1
add a comment |
ALTER AUTHORIZATION ON SCHEMA::[NT AUTHORITYSYSTEM] TO dbo
edited Nov 25 '18 at 15:17
Larnu
17.3k41630
answered Nov 25 '18 at 13:07
Joy BhattacharyaJoy Bhattacharya
1
add a comment |
ALTER AUTHORIZATION ON SCHEMA::[NT AUTHORITYSYSTEM] TO dbo
edited Nov 25 '18 at 15:17
Larnu
17.3k41630
answered Nov 25 '18 at 13:07
Joy BhattacharyaJoy Bhattacharya
1
ALTER AUTHORIZATION ON SCHEMA::[NT AUTHORITYSYSTEM] TO dbo
edited Nov 25 '18 at 15:17
Larnu
17.3k41630
answered Nov 25 '18 at 13:07
Joy BhattacharyaJoy Bhattacharya
1
edited Nov 25 '18 at 15:17
Larnu
17.3k41630
edited Nov 25 '18 at 15:17
Larnu
17.3k41630
edited Nov 25 '18 at 15:17
Larnu
17.3k41630
17.3k41630
answered Nov 25 '18 at 13:07
Joy BhattacharyaJoy Bhattacharya
1
answered Nov 25 '18 at 13:07
Joy BhattacharyaJoy Bhattacharya
1
answered Nov 25 '18 at 13:07
Joy BhattacharyaJoy Bhattacharya
1
1
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%2f35434389%2fhow-to-drop-a-sql-server-user-with-db-owner-privilege%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
