Post reply

Cannot drop the user 'dbo'

  • September 26, 2018 at 11:15 am

    #2007130

    Fernando Jacinto Alvarez - Wednesday, September 26, 2018 11:12 AM

    Brandie Tarvin - Wednesday, September 26, 2018 11:02 AM

    Fernando Jacinto Alvarez - Wednesday, September 26, 2018 9:49 AM

    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

    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.

    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.

    You are completely misunderstanding the context of my original post.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • September 26, 2018 at 11:54 am

    #2007132

    I have found instances where user accounts had been given ownership of the dbo schema.  This causes issues when you need to drop these users and can't because they own the dbo schema.

Viewing 2 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply