Today we ran into an issue where I was trying to remove someone’s rights to a database in SQL. The database had been copied in from another SQL server which means the permissions on the database rarely align with the server permissions (see my page about migrating SQL logins to another SQL server).
Every time we tried to change this user’s permissions so they weren’t a DBO, I received an error:
Cannot alter the user ‘dbo’. (Microsoft SQL Server, Error: 15150)
The fix was running a simple SQL query to update the owner of the database to be ‘sa’ rather than this user.
The fix was running a simple SQL query to update the owner of the database to be ‘sa’ rather than this user.
The command was:Use {database_name}
exec sp_changedbowner 'sa'
This was a Microsoft SQL 2014 Enterprise server, I’m assuming this probably works on Microsoft SQL as far back as 2005 but I haven’t tested it enough to validate that.
Thank you to these folks that figured it out first: http://sqlanddotnetdevelopment.blogspot.com/2013/07/cannot-alter-user-dbo-microsoft-sql.html
Thank you for visiting our website and we hope that our instructions on this issue have been helpful to you. If you found our guide informative and useful, please consider sharing it with your friends and colleagues who might also benefit from this knowledge. Your support in spreading the word about our website is greatly appreciated and will help us continue to provide valuable resources to the world. Thank you again for your time and we look forward to your continued support.
