SQL Server – Can’t Delete Login Due to DB Ownership

sql serversql server 2014

I have a SQL Server 2014 instance that's installed on an OS that I've migrated from one domain to another. The new domain and user account are from separate forests, but they bear the same descriptive names (e.g. DOMAIN\User).

Naturally, I can't log into the database with the old domain account, so I need to delete that. The trouble is that I can't.

I keep getting the error message that the login owns one or more databases. But it's very clear that it doesn't—I've cleared mappings and reassigned dbo in all databases.

Yet the error persists. This is very odd.

Is there a way to determine what's preventing me from deleting this login so I can proceed to add the same-named account from the new same-named domain?

–EDIT–

Correction: I'd reassigned dbo in all databases that displayed a login in the Login name field. That field was empty for the two RS databases, incorrectly leading me to believe that they weren't owned by the login that I was trying to delete. The script provided by @hot2use did the trick.

Best Answer

You should check the ownwers of the databases with the following script:

SELECT d.name, sl.name
    FROM sys.databases as d JOIN sys.syslogins AS sl 
        ON d.owner_sid = sl.[sid]

You might then find out that a database is linked to on of your Windows/AD accounts.

There is a slight difference between a SQL Login (native or Windows account) having the role db_owner and an account being the database owner.

If you do find a database with an account linked to the Windows/AD account you want to get rid of, then execute the following command while connected to that database:

USE <databasename>
GO
sp_changedbowner '<new_account>'

IMHO: Changing the owner to 'sa' is a viable option.

Good luck.

Related Question