Sql-server – Unable to delete SQL Server 2008 R2 login

sql serversql-server-2008sql-server-2008-r2

I had created a SQL Server login some days back. Now I need to delete the same. So I went into SQL Server Management Studio => Security => Logins and selected the login that I want to delete. It displays an informational message which says

Deleting server logins does not delete the database users associated
with the logins. To complete the process, delete the users in each
database. It may be necessary to first transfer the ownership of
schemas to new users.

Ignoring this message, when I confirm the delete process I am getting a Fatal Error which says

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

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

This login is the owner of 1 job(s). You must delete or reassign these
jobs before the login can be dropped. (Microsoft SQL Server, Error:
15170)

How do I delete the login when there are jobs (according to the message above) owned by the login.

regards,
Nirvan.

Best Answer

If you have many jobs and do not know which job is owned by this login, this query will tell you the name of jobs owned by them:

SELECT j.name
FROM msdb.dbo.sysjobs AS j
INNER JOIN sys.syslogins AS l ON j.owner_sid = l.sid
WHERE l.name = 'loginYouWantToDelete'

Once you have found the job, you can delete it using: sp_delete_job: (From MSDN)

EXEC msdb.dbo.sp_delete_job
    @job_name = N'JobToDelete';
Related Question