Cleaning up Orphaned Users and finding the owner of Databases in SQL Server

GM Guys, Its cleanup time. I am working to remove the unused logins and orphan users. When I try to delete the previous DBA login I get below error. Bad practice, he didn't change the dbowner to sa when he created.

Error:

TITLE: Microsoft SQL Server Management Studio
------------------------------

Drop failed for Login ''.  (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=14.0.17213.0+((SSMS_Rel).171128-2020)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Drop+Login&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

Login '' owns one or more database(s). Change the owner of the database(s) before dropping the login. (Microsoft SQL Server, Error: 15174)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=12.00.4237&EvtSrc=MSSQLServer&EvtID=15174&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

I have used below script to find the databases this user owned and found bunch of them. 

select suser_sname(owner_sid) As LoginName,name as DBName

  from sys.databases where suser_sname(owner_sid) ='domain\loginname'

----------------------------------------------------------------------------------------------------------

I am going to change all these databases owner to sa using below script.

USE [DBName]
GO

ALTER AUTHORIZATION ON DATABASE::[DBName] TO [sa]

GO

Now I am able to delete the login.

Removing Orphaned Users:

while I am searching for finding orphan users script I found few but the best one is in the below blog.

https://dba.stackexchange.com/questions/34976/find-orphaned-users

Here is the code:

---------------------------------------------------------------------------------

DECLARE @SQL nvarchar(2000)

DECLARE @name nvarchar(128)

DECLARE @database_id int

SET NOCOUNT ON;

IF NOT EXISTS 

    (SELECT name FROM tempdb.sys.tables WHERE name like '%#orphan_users%')

BEGIN

    CREATE TABLE #orphan_users

        (

        database_name nvarchar(128) NOT NULL,

        [user_name] nvarchar(128) NOT NULL,

        drop_command_text nvarchar(200) NOT NULL,

        drop_schema_text nvarchar(200) not null,

        drop_role_text nvarchar(200) not null

        )

END

CREATE TABLE #databases 

(

    database_id int NOT NULL

    , database_name nvarchar(128) NOT NULL

    , processed bit NOT NULL

)

INSERT

    #databases 

    ( database_id

    , database_name

    , processed )

SELECT 

    database_id

    , name

    , 0 

FROM 

    master.sys.databases 

WHERE 

    name NOT IN 

    ('master'

    , 'tempdb'

    , 'msdb'

    , 'distribution'

    , 'model')

WHILE (SELECT COUNT(processed) FROM #databases WHERE processed = 0) > 0

BEGIN

    SELECT TOP 1

        @name = database_name,

        @database_id = database_id

    FROM #databases

    WHERE processed = 0

    ORDER BY database_id

    SELECT @SQL =

'USE [' + @name + '];

INSERT INTO #orphan_users (database_name, user_name, drop_command_text, Drop_schema_text, drop_role_text)

SELECT 

    DB_NAME()

    , u.name

    , 

        ' + '''' 

        + 'USE [' + @name + ']; ' 

        + 'DROP USER [' 

        + '''' + ' + u.name 

        + ' + '''' + '] ' 

        + '''' + '

    , 

        ' + '''' + 'USE [' + @name + ']; ' +

        'ALTER AUTHORIZATION ON SCHEMA::[' 

        + '''' + ' + u.name 

        + ' + '''' + ']  TO [dbo]' + '''' + '

    , 

        ' + '''' + 'USE [' + @name + ']; ' +

        'ALTER AUTHORIZATION ON Role::[' 

        + '''' + ' + u.name 

        + ' + '''' + ']  TO [dbo]' + '''' + '

FROM

    master..syslogins l

RIGHT JOIN 

    sysusers u 

ON l.sid = u.sid

WHERE   

    l.sid IS NULL

AND issqlrole <> 1

AND isapprole <> 1

AND ( u.name <> ' + '''' + 'INFORMATION_SCHEMA' + ''''

        + ' AND u.name <> ' + '''' + 'guest' + ''''

        + ' AND u.name <> ' + '''' + 'dbo' + ''''

        + ' AND u.name <> ' + '''' + 'sys' + ''''

        + ' AND u.name <> ' + '''' + 'system_function_schema' + '''' + ')'

    PRINT @SQL;

    EXEC sys.sp_executesql @SQL

    UPDATE 

        #databases 

    SET 

        processed = 1 

    WHERE 

        database_id = @database_id;

END

SELECT 

    database_name

    , [user_name]

    , drop_command_text 

    , Drop_schema_text

    , drop_role_text

FROM 

    #orphan_users 

ORDER BY 

    [database_name]

    , [user_name];

DROP TABLE #databases;

DROP TABLE #orphan_users;

SET NOCOUNT OFF; 

-------------------------------------------------------------------------

No idea who created this but thanks to post this.