Thursday, 18 February 2010

Domain User cannot edit a SQL Job they created or own

I have come across a situation in SQL management studio where a windows domain user is unable to edit a job that they created themselves, and are still the owner of.

It seems that this is one of the rare occasions when SQL logins are case sensitive on the username.

When creating a job, SQL sets the job owner to be the name field from syslogins associated with the current user.

However, when editing a job, SQL checks the current user with SUSER_SNAME (which gives the actual domain login) and does a case sensitive comparison with the job owner.

Example for the slow ones at the back - This means if you have created a SQL login for user “GrumpyDomain\Grumpy Dba”, and he creates a job, that is the value that will be assigned to the job owner.

However, if that users account is actually “GrumpyDomain\Grumpy DBA”, then they will be unable to edit that job.

To fix it you can use ALTER LOGIN to change the account, e.g.

ALTER LOGIN [GrumpyDomain\Grumpy Dba], [GrumpyDomain\Grumpy DBA]

What makes me really grumpy is the fact that we are running this to fix an issue with logins being case sensitive, but this ALTER LOGIN statement itself isn’t actually case sensitive, so even this would work …

ALTER LOGIN [GrumpyDomain\GrUmPy dBA, [GrumpyDomain\Grumpy DBA]

Nice to have consistency.

No comments:

Post a Comment