User is not showing for abc database under security folder but when we query from sys.databases it showing as a db owner

Question

Below Steps I Did

EXEC sp_changedbowner 'domainname\user' 

... to change the principal owner.

It was executing for a long time so i cancelled it. While closing the tab it asked me to save the uncommitted transactions, so I saved them.

Later when I checked under the database security folder the old user name was not showing any more.

But when I ran the query

select database_id, name, suser_sname(owner_sid) from sys.databases 
where suser_sname(owner_sid) = 'domain\olduser'

...it returns one row which shows that the old user is still the db owner.

If I check the database properties from SSMS it shows the old user as the owner as well. But under the database users I am not able to see the old user any more.

Please suggest how I can change the db_owner...

Answer 1

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)

1. 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).

2. 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.

1. DROP LOGIN <old_account>

That's it. You're finished.