Cannot drop the user 'dbo'
-
I used to know how to do this, but nothing I try is working.
My personal login is associated with a database in a HAG. It's the primary instance of the db, so I can change things. I was the owner, now I'm not (changing the database owner was the first thing I tried). My login is not associated on the database level of security (I checked via SSMS and while the server level security has my login tied to the database, my login is not shown on the list of "local" user accounts on the db level). I've tried running "ALTER AUTHORIZATION" and sp_change_users_login (just in case some accounts were orphaned) but no joy. No matter what I do, I can't seem to unassociated myself from the dbo schema on the database. I can't even uncheck db_owner on the server security level because it tells me "Cannot use the special principal 'dbo'."
Now I'm really confused because I'm not even trying to use it. I'm trying to remove it. Note: My login is actually part of a windows group login that is sysadmin. Not sure if this makes a difference in the behavior I'm seeing or not.
As a final test, I tried creating a new user under the database with my personal login for the server level login and got this message "'Login' is not a valid login or you do not have permission."
Any thoughts on this one?
-
What do you get if you run xp_logininfo [domain\yourpersonalaccount], @option = 'all' ?
- Beatrix Kiddo - Monday, September 24, 2018 6:09 AM
account name type privilege mapped login name permission path
[domain\yourpersonalaccount] user admin [domain\yourpersonalaccount] [domain\SysadminDBAGroupAcct] -
Ok, so you're only permissioned as a sysadmin, then (through membership of that group). That's why.
- Beatrix Kiddo - Monday, September 24, 2018 6:46 AM
"That's why" what?
And do you have suggestions on how I can resolve the issue?
- Brandie Tarvin - Monday, September 24, 2018 5:38 AM
Are you saying that your login owns the db_owner schema?
- Lynn Pettis - Monday, September 24, 2018 7:41 AM
That's what it looks like. When I look at the schema in the GUI, however, it says dbo owns dbo. So I'm not sure exactly how to fix this. Ordinarily, I'd just update the owner right there in the schema properties box.
-
Oh, bother. I wonder if I granted IMPERSONATE or some other permission that's screwing this up.
I need to check.
-
Oh, wow. This is really weird.
When I looked in the GUI, my view said my login was mapped to database A. When my coworker looked in the GUI, he saw my login mapped to database B.
He fixed database B and suddenly we're able to remove the permissions. Apparently I was trying to fix the issue on the wrong DB because my view was hosed somehow. Not sure how that happened.
-
....AND I own an endpoint for the HAG. When trying to fix it, I get "Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.".
Joy. Hopefully my coworker can run the code for me and get this working.
- Brandie Tarvin - Monday, September 24, 2018 8:23 AM
You should be able to change the ownership of the endpoint yourself:
ALTER AUTHORIZATION ON ENDPOINT::{name} TO sa;
If that doesn't work - then it may require dropping/recreating the endpoint which is going to be a bigger issue.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs - Jeffrey Williams 3188 - Monday, September 24, 2018 1:46 PM
One would think. But that's the command I used (except I used a different account than sa) and it gave me the error I reported.
My coworker changed it just fine. And it's possible I just needed to login with my admin account or a SQL Login sysadmin account to do it myself.
-
You should not drop users as dbo because is a system default user created for all dbs and sa is mapped to most of dbo objects all sysadmins users needs dbo to execute any activity more info at: https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/ownership-and-user-schema-separation-in-sql-server
- Fernando Jacinto Alvarez - Wednesday, September 26, 2018 9:49 AM
You should not use individual users as dbo in the first place because when someone leaves the company or the team, it causes worse issues. And unfortunately, individual users get stuck with the dbo ownership when they create a database or get added as the very first user credential in the database (this happens automatically). If Microsoft would fix it so that this wouldn't happen automatically and the DBA had to choose which account got stuck owning dbo, this wouldn't be a problem.
So, yes, I'm going to continue to drop the user owning dbo after I switch dbo to a different user because SQL Server is stupid sometimes.
- Brandie Tarvin - Wednesday, September 26, 2018 11:02 AM
you can´t use dbo as a user, dbo is only a schema owner and all objects should be created on dbo schema to avoid duplicates as a best practice, but wherever you want is your db and if you want to avoid issues you can use windows based groups instead of sql server based users and make sure that windows users aren't own any object, maintenance plan or database job, the idea is to avoid same table on different schemas like you can see on selected tables and don´t have to assign permissions to each table or any other object.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply