- Remove From My Forums
-
Question
-
Hi there,
I have three databases — test, test2 and prod. For all three, my database owner set to a specific domain user — let’s say «DOMAINappuser». When I r-click database properties, the correct owner displays (DOMAINappuser).
In test and test2, «DOMAINappuser» is mapped to a named user in the database — «appuser».
In prod, «DOMAINappuser» is mapped to the «dbo» user. I need to change this so it maps to «appuser», but I get the message «Cannot alter the user ‘dbo’.
1) Security > r-click «DOMAINappuser» > Properties > User Mapping. Change User and Default Schema from dbo to appuser.
Error: Cannot alter the user ‘dbo’.
2) Then I tried this: Change the db owner to some other user. Go into Login Properties > User Mapping and change User and Default Schema from dbo to appuser. Then change the db owner back to «DOMAINappuser». But I get the error: «The
proposed new database owner is already a user or aliased in the database.»-
Edited by
Tuesday, May 12, 2015 3:22 PM
-
Edited by
26
Thursday
Apr 2012
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
I’ve recently performed an in-place upgrade of the default instance of a SQL Server 2016 to SQL Server 2019 (CU11).
I’m trying to add the Polybase feature but am unable to resolve this error that occurs during installation:
Error installing SQL PolyBase
The proposed new database owner is already a user or aliased in the database. Changed database context to ‘DWQueue’.
Error code: 0x84BB0001
The following error dialog appears during the installation process
Retry just results in the same message, after clicking Cancel the installation continues and then finishes with the install dialog showing the following:
If I re-run the setup it thinks Polybase is installed (however it’s not, connecting using SSMS still informs me I have to install Polybase). I have to manually run msiexec /x {6FDD1CAA-79C4-43B0-9483-5BD503E656DC} to remove it before I can retry.
During setup I provided domain accounts for the service setup, there’s nothing else to configure.
Can anyone shed any light on this error and what I can do to install successfully?
Edit
When in the setup process and presented with the Service Accounts screen for Polybase Engine and Polybase Data Movement, the text at the top of the screen states «Microsoft recommends that you use separate account for each SQL Server service.«
However if I specify a different Account Name & password for these services it produces an error and you cannot proceed:
Sun Jan 13, 2008 by Dan Guzman
Do you know who owns your databases? Execute sp_helpdb on your SQL Server instances and you might find some surprises under the “owner” column. It isn’t uncommon to see accounts of people who have left the company or moved on to other roles in the organization that don’t require privileged database access. Yet these owners still have full database permissions, including the ability to drop the database. To prevent these security issues and other problems, consider establishing an appropriate database ownership standard for your environments.
Database ownership is an often forgotten detail because it is implicitly set to the database creator’s account. The owner will initially be a Windows account or SQL login, depending on the authentication method used by the creator. Note that the owner is always an individual account, not a group or role, so a database created by a sysadmin role member is actually owned by the creator’s individual account instead of a built-in security principal (unless the creator logged in using the “sa” account).
A Best Practice is to change the database owner immediately after creating, restoring or attaching a database. Unless I have a reason to do otherwise, I specify “sa” as the database owner. This can be done with sp_changedbowner in SQL 2000 or with ALTER AUTHORIZATION in SQL 2005:
|
SQL 2000: |
EXEC MyDatabase..sp_changedbowner ‘sa’; |
|
SQL 2005 and SQL 2008 |
ALTER AUTHORIZATION ON DATABASE::MyDatabase to sa; |
The Significance of the Database Owner
Database ownership is important from a security perspective because the owner account is mapped to the built-in “dbo” user. The “dbo” user, sysadmin role members and db_owner role members all have full database permissions and can also DROP the database. The database owner is also used as the authorization of the “dbo” schema, which comes into play with ownership chaining. With cross-database chaining, the databases involved must have the same owner in order to provide an unbroken chain for “dbo” schema objects.
A difference between the database owner and db_owner role members is that there is exactly one “dbo” user (the database owner) but there may be many users that are db_owner role members. The owner’s account cannot be explicitly added to the database because the owner is already implicitly mapped to the “dbo” user and an account can be mapped to no more than one user per database. If you attempt to add the owner as a database user, error message “The proposed new database owner is already a user or aliased in the database” results.
Troubleshooting Database Ownership
The database owner is ultimately identified by the account SID (security identifier). The creator’s account SID is recorded in 2 places: 1) at the server level in sys.databases/sysdatabases and 2) in the database as the dbo user SID in sys.database_principals/sysusers. These SIDs will normally match but can get out-of-sync following a database restore or attach. You will also end up with a NULL database owner if the owner’s Windows account is deleted because of the orphaned SID.
Mismatched owner SIDs can result in problems such as
· Problems executing system stored procedures
· Problems with tools
· Broken cross-database ownership chains
The sample queries below will help identify problem database owners. Problem owners will have mismatched SIDs and/or NULL owner names. Fortunately, problem database ownership is easy to remedy. Simply change the database owner using sp_changedbowner or ALTER AUTHORIZATION as you would after creating a new database. In some cases, you might get an erroneous “The proposed new database owner is already a user or aliased in the database” due to the mismatch. A workaround in this situation is to temporarily change the database owner to a non-conflicting login and then back to the desired owner.
|
SQL 2000: |
IF OBJECT_ID(N‘tempdb..#owners’, ‘U’) IS NOT NULL DROP TABLE #owners; CREATE TABLE #owners ( database_name sysname NOT NULL, sys_databases_sid varbinary(85) NOT NULL, sys_databases_owner nvarchar(256) NULL, sys_users_sid varbinary(85) NULL, sys_users_owner nvarchar(256) NULL ); INSERT INTO #owners ( database_name, sys_databases_sid, sys_databases_owner ) SELECT name, sid, SUSER_SNAME(sid) FROM master.dbo.sysdatabases; EXEC sp_MSforeachdb ‘ UPDATE #owners SET sys_users_sid = ( SELECT sid FROM [?].dbo.sysusers WHERE name = »dbo»), sys_users_owner = ( SELECT SUSER_SNAME(sid) FROM [?].dbo.sysusers WHERE name = »dbo») WHERE database_name = »?» ‘; SELECT * FROM #owners WHERE sys_databases_sid <> sys_users_sid; IF OBJECT_ID(N‘tempdb..#owners’, ‘U’) IS NOT NULL DROP TABLE #owners; GO |
|
SQL 2005: |
IF OBJECT_ID(N‘tempdb..#owners’, ‘U’) IS NOT NULL DROP TABLE #owners; CREATE TABLE #owners ( database_name sysname NOT NULL, sys_databases_sid varbinary(85) NOT NULL, sys_databases_owner nvarchar(256) NULL, sys_users_sid varbinary(85) NULL, sys_users_owner nvarchar(256) NULL ); INSERT INTO #owners ( database_name, sys_databases_sid, sys_databases_owner ) SELECT name, owner_sid, SUSER_SNAME(owner_sid) FROM sys.databases; EXEC sp_MSforeachdb ‘ UPDATE #owners SET sys_users_sid = ( SELECT sid FROM [?].sys.database_principals WHERE name = »dbo»), sys_users_owner = ( SELECT SUSER_SNAME(sid) FROM [?].sys.database_principals WHERE name = »dbo») WHERE database_name = »?» ‘; SELECT * FROM #owners WHERE sys_databases_sid <> sys_users_sid OR sys_databases_owner IS NULL; IF OBJECT_ID(N‘tempdb..#owners’, ‘U’) IS NOT NULL DROP TABLE #owners; GO |
Legacy Comments
|
|
|
|
JohnFx |
re: Database Owner Troubles Good article. Database ownership in SQL seems like it is often more complicated than it really needs to be and can get really confusing. |
|
|
|
|
Dan Guzman 2008-01-17 |
re: Database Owner Troubles I agree that database ownership and login/user mapping is unnecessarily complicated. I blame Microsoft SQL Server’s Sybase roots along with the need to maintain backwards compatibility. |
|
|
|
|
Bob Martin |
re: Database Owner Troubles This helps answer another question I had posted in the newsgroups about EXECUTE AS failing when the database was created in a machine account vs my account. For whatever reason, databases created under the machine account mapped dbo to sa, where as databases created in other accounts were mapped to the login. For now, I know I need to add to my scripts changing dbo to sa when I create databases. |
|
|
|
|
Peter |
re: Database Owner Troubles Very good article! |
|
|
|
|
Reddy Kiran |
re: Database Owner Troubles Nice Helpful Article |
|
|
|
|
croware |
re: Database Owner Troubles you’re great man! good article |
|
|
|
|
Idol |
re: Database Owner Troubles Really good, thank you. |
|
|
|
|
rocky |
re: Database Owner Troubles instead of ‘sa’ can we have ‘NT AUTHORITYSYSTEM’ as owner of the database. If not can you explain why? the reason i am asking this because we are facing the similar scenario as stated above(owner leaving the team), last week unfortunately one of my team member removed the admin group and we dont have sa password, then we have to drop that database and restore it from the backup luckily we have that one. |
|
|
|
|
Dan Guzman 2009-03-17 |
re: Database Owner Troubles Yes, you can instead use a built-in Windows principal if you prefer. Be aware of the security implications, though. Processes running under that Windows principal will have database owner permissions in that database. |
|
|
|
|
Mike Robens |
re: Database Owner Troubles Thankyou, you have explained the problem perfectly and the solution works a treat. |
|
|
|
|
Chandrasekhar kannali |
re: Database Owner Troubles Thank you very much.It is very good and usefull article. |
|
|
|
|
alphatross |
re: Database Owner Troubles An alternative to using ‘sa’ as the ‘dbo’ for all Databases is to create an Active Directory «service account» style user named after the Database and make that dbo. The advantage is that it only has access to one database, and extra info such as a contact for the database can be stored in the AD User object. Also, Linked Servers can alias to this Windows account to access a database (there are distributed query performance advantages to this, but obvious security drawbacks). The Windows account can be set to have zero Windows permissions and even interactive Login rights revoked. |
|
|
|
|
Innocent Gumbo |
re: Database Owner Troubles Dan, I am impressed by this. Yes I am especially on explaining the Role(db_owner) and the user( db owner). Just at the rignt time for me. We usually get confused by the 2. You just stated that the role can have as many users mapped to it and the other only applies once in a Database!! This is cool. Thanks |
|
|
|
|
vibhas |
re: Database Owner Troubles Thanks for sharing vital info regarding databases. |
|
|
|
|
steve |
re: Database Owner Troubles I just checked mine and nearly swore with what I found! Guess I found out just in time! |
|
|
|
|
JBL |
re: Database Owner Troubles Excellent article. Can anybody comment on the following. We have an application that utilises SQL. We have found one of our customers using the data base directly to run report writing functions. Does anybody have any idea on where we stand legally |
|
|
|
|
Gangu |
re: Database Owner Troubles Who is the Owner of ‘sa’ login? dbo or sysadmin |
|
|
|
|
guzmanda 2011-02-20 |
re: Database Owner Troubles I’m not sure I understand your question. Logins do not have owners. All members of the sysadmin server role, including the ‘sa’ login, become the dbo user in all databases even if the login does not actually own the database. |
|
|
|
|
Jakub Berezanski |
re: Database Owner Troubles A small improvement to the sample queries: surround the ? in the FROM clauses with square brackets to account for special characters in database names (e.g. «Sample-DB-Name»): FROM [?].sys.database_principals |
|
|
|
|
guzmanda 2011-02-23 |
re: Database Owner Troubles Good point about enclosing the database name. The best practice in object naming is to conform to identifier naming rules so that one doesn’t need to enclose database names. But robust code should work regardless so I’ll change the scripts. Thanks for the suggestion. |
|
|
|
|
gregrae |
re: Database Owner Troubles You’re awesomge Dan Guzman! Thanks for solving my issue. |
I currently have a website hosted developed with kentico 7. I tried importing the exported website into my localhost and that failed. In my findings, i realised that each table in the online version has a bvs (the database user created) prefix. I have tried creating the same scenario on my localhost without any luck. Does anyone have an idea ?
Here is the error displayed at importation.
asked Apr 22, 2015 at 15:14
PeterPeter
1832 gold badges3 silver badges13 bronze badges
4
You can change the schema owner using this command:
ALTER AUTHORIZATION ON SCHEMA::bvs TO db_owner;
answered Apr 22, 2015 at 15:36
AlexAlex
20.9k10 gold badges61 silver badges72 bronze badges
Try this out:
USE MyDB
GO
ALTER AUTHORIZATION ON SCHEMA::bvs TO dbo;
GO
SP_DROPUSER 'bvs'
GO
SP_CHANGEDBOWNER 'bvs'
answered Apr 22, 2015 at 15:34
FutbolFanFutbolFan
12.8k3 gold badges23 silver badges35 bronze badges
7