You do not receive any error messages or warning messages when you remove a SQL Server login account that owns one or more jobs in SQL Server 2000 (885713)


The information in this article applies to:

  • Microsoft SQL Server 2000 (all editions)

Bug #: 303829 (SQL BU Defect Tracking)

Bug #: 472552 (SQL Server 8.0)

SYMPTOMS

When you remove a Microsoft SQL Server login account that owns one or more SQL Server Agent jobs, you may notice that the SQL Server login account is removed successfully and you do not receive any error messages or warning messages.

Additionally, you may notice that all the jobs that were owned by the removed SQL Server login account are now owned by a user account that is a member of the sysadmin fixed server role.

This problem occurs if you remove the SQL Server login account after you connect to an instance of Microsoft SQL Server 2000 as a user who is a member of the securityadmin fixed server role and is not a member of the sysadmin fixed server role.

Note If you try to remove a SQL Server login account that owns one or more jobs when you are connected to the instance of SQL Server 2000 as a member of the sysadmin fixed server role, you will receive the following error message:
Server: Msg 14248, Level 16, State 1, Procedure sp_droplogin, Line 112
This login is the owner of NumberOfJobsThatAreOwnedByTheUser job(s). You must delete or reassign these jobs before the login can be dropped.

CAUSE

The members of the securityadmin fixed server role do not have permissions to access the data in the tables and views that are related to jobs in the msdb system database. Therefore, while checking for the dependencies on the SQL Server login account that is being removed, the information thatis related to the jobs that are owned by the SQL Server login account is not available. Therefore, SQL Server assumes that the SQL Server login account does not own any jobs, and then SQL Server drops the SQL Server login account.

WORKAROUND

If you must use a member of the securityadmin fixed server role to manage the SQL Server login accounts, a member of the sysadmin fixed server role must check job ownership and reassign the job appropriately in SQL Server 2000 before it removes the SQL Server login accounts.

MORE INFORMATION

When you try to remove a SQL Server login account, SQL Server runs the msdb.dbo.sp_check_for_owned_jobs stored procedure. The msdb.dbo.sp_check_for_owned_jobs stored procedure accesses the msdb.dbo.sysjobs_view view to collect the details of the jobs that are owned by the specified SQL Server login account. The members of the securityadmin fixed server role do not have access to the tables, views, and stored procedures in the msdb system database. Therefore, even when the msdb.dbo.sysjobs_view view contains rows that are related to the jobs that are owned by the SQL Server login account that you want to remove, no rows are returned if you are connected to SQL Server as a member of the securityadmin fixed server role.

Steps to reproduce the behavior

  1. Start SQL Query Analyzer, and then connect to your instance of SQL Server 2000 as a member of the sysadmin fixed server role.
  2. Create a SQL Server login account that is named SecAdminUser, and then add the SecAdminUser SQL Server login account as a member of the securityadmin fixed server role. To do this, run the following Transact-SQL statements in SQL Query Analyzer:
    SP_ADDLOGIN 'SecAdminUser','SecAdminUser'
GO
SP_ADDSRVROLEMEMBER 'SecAdminUser','securityadmin'
GO
  3. Create another SQL Server login account that is named JobOwningUser, create a SQL Server Agent job that is named TestJob, and then set the JobOwningUser SQL Server login account as the job owner. To do this, run the following Transact-SQL statements in SQL Query Analyzer:
    SP_ADDLOGIN 'JobOwningUser','JobOwningUser'
GO

USE msdb
GO

SP_ADD_JOB 
	@job_name = 'TestJob',
	@enabled = 1,
	@start_step_id=1,
	@owner_login_name = 'JobOwningUser',
GO

SP_ADD_JOBSERVER 
	@job_name = 'TestJob',
	@server_name = 'SQLServerInstanceName'
GO

SP_ADD_JOBSTEP 
	@job_name = 'TestJob',
	@step_id = 1,
	@step_name = 'Test job step',
	@subsystem = 'TSQL',
	@command = 'Select * from master..sysobjects'
GO
  4. Notice that if you try to remove the JobOwningUser SQL Server login account by using the sp_droplogin system stored procedure, you receive the following error message:
    Server: Msg 14248, Level 16, State 1, Procedure sp_droplogin, Line 112
    This login is the owner of 1 job(s). You must delete or reassign these jobs before the login can be dropped.
  5. Close SQL Query Analyzer.
  6. Start another instance of SQL Query Analyzer, and then connect to your instance of SQL Server by using the SecAdminUser SQL Server login account.
  7. Remove the JobOwningUser SQL Server login account by using the sp_droplogin system stored procedure. To do this, run the following Transact-SQL statements in SQL Query Analyzer:
    SP_DROPLOGIN 'JobOwningUser'
GO
    Notice that the JobOwningUser SQL Server login account is removed and you do not receive any warning messages or error messages.

REFERENCES

For more information, visit the following Microsoft Developer Network (MSDN) Web sites:

Removing Logins and Users
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_security_8g6r.asp

sp_droplogin
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_drop_2q0e.asp

Modification Type:MajorLast Reviewed:9/20/2004
Keywords:kbView kbStoredProc kbSysAdmin kblogin kberrmsg kbtshoot kbprb KB885713 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.