The database owner displayed in the files section of the database properties is not displayed in the Security branch of the database. This is by design.
Explanation
Database Owner
The database owner in the file setting of the database properties is the owner of the database and does not have to be explicitly listed in the security tab. This is normally the SQL Login that created the database.
If a Windows Account (e.g. DOMAIN\ACCOUNT
) was logged in and using SSMS to create the database, then this will be the database owner.
If sa
or another SQL Login was logged in and using SSMS to create the database, then this will be the database owner.
db_owner Role
The db_owner role can be assigned to individual database users. These can be Windows Accounts or SQL Server Logins.
Members of the db_owner fixed database role can perform all configuration and maintenance activities on the database, and can also drop the database in SQL Server. (In SQL Database and SQL Data Warehouse, some maintenance activities require server-level permissions and cannot be performed by db_owners.)
Reference: Database-Level Roles (Microsoft Docs)
These are similar to the database owner in that they have the same permissions, but are explicitly defined in the database. That is why they are displayed in the security branch of the database.
Solution
To change the database owner ...
Assign new database owner
To remove a Database Owner (not the db_owner
role) you have to assign a new database owner. This can be achieved using the mentioned sp_changedbowner
stored procedure (deprecated) or the ALTER AUTHORIZATION...
syntax.
Reference: ALTER AUTHORIZATION (Transact-SQL) (Microsoft Docs)
ALTER AUTHORIZATION ON DATABASE::<DB_name> TO <new_owner>;
a. replace <DB_name>
with the name of the database
b. replace <new_owner>
with a Windows Account (e.g. DOMAIN\ACCOUNT
) or a SQL Login (e.g. sa
).
sp_chnagedbowner '<new_owner>'
b. replace <new_owner>
with a Windows Account (e.g. DOMAIN\ACCOUNT
) or a SQL Login (sa
).
Remove old SQL Login
Now that the database has a new database owner, you can drop the SQL Login.
DROP LOGIN <old_account>
That's it. You're finished.