One of the junior DBA of my previous organisation recently asked me about this error which he was receiving while changing the database owner of one database in QA environment. The error is pretty much self-explanatory. But I thought I should write about it which may help some DBAs.
Below is the exact error:
Msg 15110, Level 16, State 1, Line 1
The proposed new database owner is already a user or aliased in the database.
The reason for this error is- the user which we are trying to make the owner of the database, is already a user for the same database. The fix for this error is pretty simple. For the demo, I have a database called MyDB and let’s try to change the database owner of the database MyDB to ‘test_user’
USE MyDB
GO
SP_CHANGEDBOWNER ‘test_user’
And we are getting the error:
Msg 15110, Level 16, State 1, Line 1
The proposed new database owner is already a user or aliased in the database.
To fix the same, lets take the following steps:
USE MyDB
GO
SP_DROPUSER ‘test_user’ — Dropping test_user from the user database
GO
SP_CHANGEDBOWNER ‘test_user’ — Changing the OWNER of the database
Hope this helps.
Cheers,
Subhro Saha
Steve said:
Hi Subhro,
Your quick guide helped – thanks for saving me the time of digging this up myself. I’m no DB expert so this has helped greatly.
Karan Guleria said:
Hi Subhro,
I need some help with ‘Allowing a group of users access to a particular DB’.
I have 20 DB’s in SQL and I am using MS SQL Server Studio 2008 R2. There are 2 group of developers – A & B. Right now when they login to there respective machines with windows authentication in SQL, they are presented with all the DB’s and I do not want that. I want them to see just the DB that they would be working on. Your article (which is awesome & I appreciate it) describes how one user can take overship of the DB. I understand that a group or a role can not be an owner of a DB – that is what SQL server studio 2008R2 says. How can I achieve what I want to, if at all it can be achieved.
Thank you for your time,
Karan Guleria –
Subhro Saha said:
Hi Karan,
Please refer https://subhrosaha.wordpress.com/2013/02/02/sql-server-hiding-the-list-of-databases-in-ssms/
Hope this helps.
Cheers,
Subhro Saha
Karan Guleria said:
Subhro,
Thank you so very much for getting back to me with your response.
My situation is regarding giving a group of users. The group of users when login should be able to see only one designated DB. Doing this for one particular user is achieved by Denying to view all DB’s and then making the designated user the owner of that DB. I wasn’t able to find anything over the internet that showed how the same can be achieved for a group of users. Is this a design limitation altogether?
Please advise.
Thanks a ton bro.
Cheers!
Karan –
Subhro Saha said:
Hi Karan,
If you have a group of users and you want them to assign to a particular Database then you can achieve this by Denying view permission and creating users in the respective database and assigning DBO schema ..this way they can only see their respective database. But there is a flaw that I highlighted in the blog as well, they wont be able to see the databases in Object Explorer hence they cannot perform any GUI activities. But they can run all the queries in their respective databases. If you want them to access the database from Object Explorer then that is a design limitation of SQL Server.
Let me know if this answers your question.
Thanks
Subhro Saha
Keith said:
Hi Subhro,
Am having exactly the problem you describe, and when I follow the syntax you provide
use SharePoint_Config
exec sp_dropuser ‘SharePointServerFarm’
exec sp_changedbowner ‘SharePointServerFarm’
go
I get the following error message:
Msg 15008, Level 16, State 1, Procedure sp_dropuser, Line 12
User ‘SharePointServerFarm’ does not exist in the current database.
Msg 15110, Level 16, State 1, Line 1
The proposed new database owner is already a user or aliased in the database.
I am new to SQL Server and appreciate any help you can give me.
Thanks!
Anonymous said:
That helped! Thanks!
winston said:
Great help and so simple…thanks Subhro..
Matt said:
Just what I needed, thanks!
Ash said:
Hi Subhro,
This is what I get when I run this –
The database principal owns a service in the database, and cannot be dropped.
What am I missing?
Subhro Saha said:
Please refer to this “https://subhrosaha.wordpress.com/2014/02/05/sql-server-error-15138-the-database-principal-owns-a-schema-in-the-database-and-schema-cannot-be-dropped” .
Let me know if you need any other clarification.
Thanks,
Subhro Saha
sqlriz said:
Thanks for posting this. It helped me
Pingback: Fix The Server Principal Already Exists Windows XP, Vista, 7, 8 [Solved]
Anonymous said:
Thanks
granadacoder said:
This post saved the day! Thanks