Drop Failed for Login ‘User xxx’

How To Drop Users Who Are Owners or Databases and Jobs

When you want to drop users you need to en sure that those users are not owners databases SQL Server agent jobs, otherwise you will be confronted with the below error:

To avoid this you will need to change both the database and SQL Server agent jobs ownerships.

Change Database Ownerships

The following script will change the owners of the all the databases to sa.

EXEC sp_MSForEachDB
 'Use ?;IF DB_ID() > 4 AND DB_NAME() <> ''distribution'' EXEC sp_changedbowner ''sa'''
 

Since you are not allowed change the ownership of system databases you need to include IF DB_ID() > 4 AND DB_NAME() <> ”distribution” to the script

Change SQL Server Agent Jobs Onwership

To change the Agent job ownerships you need a cursor to iterate through the SQL Server agent Jobs.

use msdb
go
DECLARE @job_count INT
SELECT @job_count = COUNT(distinct(SUSER_SNAME(owner_sid)))
FROM msdb..sysjobs WHERE suser_sname(owner_sid) <> 'sa'
IF @job_count > '0'
    BEGIN
        DECLARE @change_job_id VARCHAR(50)
        DECLARE job_id_cursor CURSOR FOR
        SELECT job_id, name    FROM msdb..sysjobs WHERE suser_sname(owner_sid) <> 'sa'
        OPEN job_id_cursor
        FETCH NEXT FROM job_id_cursor
        INTO @change_job_id
        WHILE @@FETCH_STATUS = 0
            BEGIN
                DECLARE @sql_statement NVARCHAR(255)
                EXEC msdb..sp_update_job @job_id = @change_job_id, @owner_login_name ='sa'
        FETCH NEXT FROM job_id_cursor INTO @change_job_id
END
CLOSE job_id_cursor
DEALLOCATE job_id_cursor
END

After this you can safely drop the user.

Reference : Pinal Dave ( http://www.sql-server-performance.com/2012/drop-failed-for-login-user-xxx/ )