2

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

  • Step 1 : EXEC sp_changedbowner 'domainname\user' to change the principal owner. – Debashis Aug 24 '17 at 9:20
  • Step 1 : 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 uncommited transactions so saved it. when I checked under the database security folder the old user name was not showing any more . but when I run 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. – Debashis Aug 24 '17 at 9:30
  • End Requirement is to remove that user completely from that instance but with out changing the ownership that is not possible. – Debashis Aug 24 '17 at 9:31
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.

  • ALTER AUTHORIZATION ON DATABASE::<DB_name> TO <new_owner>; did the trick for me , but it took almost 45 mins to complete it. – Debashis Aug 25 '17 at 5:20
  • @Debashis Did my answer provided a solution for your issue? Is there anything missing in my answer? – John aka hot2use Aug 31 '17 at 10:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.