Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
You will have to first change ownership of the job, or delete the job, before you are able to delete the login. –  Adam Wenger Nov 9 '11 at 1:11
add comment

migrated from stackoverflow.com Nov 10 '11 at 1:28

This question came from our site for professional and enthusiast programmers.

2 Answers

up vote 6 down vote accepted

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 sp_delete_job
    @job_name = N'JobToDelete';
share|improve this answer
    
I see the Job now with your query. This was the job that Asp.Net created for managing its sessions. I have already deleted the database that was used by ASP.net for managing sessions. So now I would like to delete that JOB. Unfortunately, I am running an Express version of Management Studio and it does not have SQLServer Agent listed in the Object Explorer. Any advice on how do I go about deleting the job ? –  Nirvan Nov 9 '11 at 1:41
1  
I have updated my answer to include how to delete a job. –  Adam Wenger Nov 9 '11 at 1:44
    
Thanks a lot Adam. Deleted the job and login successfully. –  Nirvan Nov 9 '11 at 2:21
    
Not a problem, glad I could help. –  Adam Wenger Nov 9 '11 at 2:24
add comment

You need to set the owner of the jobs to a different login. If you look at the properties of the jobs you will see who the owner is for them. All you have to do is change that to another login.

Here is the T-SQL to change the owner of the job:

use msdb
go

exec sp_update_job @job_name= 'Your Job Name', 
@owner_login_name= 'Your New Job Owner'
go

As for the database users that are mapped to the login you are trying to drop, if you want to keep the database users and their security context just do this to them:

alter user YourDbUser 
with login = TheLoginThatYouWantTheUserToBeMappedTo
share|improve this answer
    
+1 for help with remapping users & general good answer. Nice work Shark, as usual it seems :) –  Adam Wenger Nov 9 '11 at 1:19
    
@AdamWenger thank you! I like your query with finding the jobs that a login owns. +1 for that! –  Thomas Stringer Nov 9 '11 at 1:22
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.