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