cannot reassign dbo to another user

Question

cannot reassign dbo to another user

fred2002

SSCrazy

Points: 2943
More actions
February 10, 2009 at 3:27 am

#130798

I restored a DB backup from a supplier.
And there is a login assigned to the user dbo.
I would like to drop this relationship between dbo and the login name eu\genetica
The problem is i cant delete the dbo schema, the dbo user and the user eu\genetica is not existent on my server
What can i do?

Viewing 6 posts - 1 through 5 (of 5 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

because the backup came from your supplier, and not from your server, the user "eu\genetica" is "orphaned...that is, every server in the work may have a user named "Bob", bu tbehind the scenes in syslogins, there is a unique SID tied to that login.

the user "eu\genetica" was probably from your suppliers domain, and you would not have the same user. You can drop that user, and add a new user with the right of dbowner, and use that o access the tables.

if you need to, you can consider using sp_changeobjectowner if you want to change the owner from dbo to some other login.

fred2002 (2/10/2009)
I restored a DB backup from a supplier.
And there is a login assigned to the user dbo.
I would like to drop this relationship between dbo and the login name eu\genetica
The problem is i cant delete the dbo schema, the dbo user and the user eu\genetica is not existent on my server
What can i do?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SQLPhil SSCarpal Tunnel Points: 4204 More actions · Answer 2

You can reassign the dbo user to another login or your choice using the sp_changedbowner stored procedure. This should then remove the "eu\genetica" user from the database. Make sure that the login you're assigning the dbo role to is not an existing user on the database or you'll get an exception.

fred2002 SSCrazy Points: 2943 More actions · Answer 3

there is something i dont understand.

If i reassign the dbo role to another user, it should be a Database user.

EdVassie SSC Guru Points: 60472 More actions · Answer 4

The sp_changeobjectowner procedure has a gotcha. You cannot reasign ownership to an existing database user.

The solution is simple. Drop the user you want to be the new owner from the database. Then run sp_changeobjectowner, which will add the user back into the database and assign database ownership to it.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

fred2002 SSCrazy Points: 2943 More actions · Answer 5

fred2002

SSCrazy

Points: 2943

February 11, 2009 at 3:03 am

#941947

ok i got it

Thanks