Owner of the job will not be able to modify/edit the job In SQL SERVER

Problem Description:

=========================

The owner of the job is a Windows login in SQL and cannot modify the job which he owns. This issue will occur if the Windows login added to SQL and the case of the login does not match.

Steps to Repro:

=================

1. Create a login in Windows as TEST (all in upper case)

2. Add this login to SQL Server. Use the below syntax:

CREATE LOGIN [computername\test] FROM WINDOWS --(Make sure you give test in lower case)

3. You can give SQLAgentUserRole or SQLAgentOperatorRole to this login in MSDB, so that this login can create and edit the jobs.

4. Use the below syntax to grant SQLAgentUserRole to the "computername\test" login

USE [msdb]

      GO

EXEC sp_addrolemember N'SQLAgentUserRole', N'computername\test'

      GO

5. Now, connect to SQL Server Management Studio using the test login you created and create simple job in the SQL Server Agent.

6. Now, right click on the job and click on properties.

7. Click on Steps. You should see all the options are greyed out and only view button is highlighted.

Cause:

===========

This issue occurs because we check whether the user trying to edit the permission is owner. We get the user login information using function SUSER_SNAME(). The SUSER_SNAME() function returns the user name in same case as in Windows. Then, we make a case sensitive comparison between the job owner and user name fetched using SUSER_SNAME(), which will restrict you to make any changes to the job.

Resolution:

================

You can rename the login either in Windows or in SQL. You can use the below syntax to rename the login in SQL to match the case with Windows:

ALTER LOGIN [computername\test] WITH NAME=[computername\TEST]

This is applicable to Windows Authenticated Logins (Domain/Local Windows Accounts) which do not have sysadmin rights on a SQL Server 2005 instance.

Santosh Goli
SE, Microsoft Sql Server

Reviewed By

Amit Banerjee & Akbar Farishta
Technical Lead, Microsoft Sql server