Sql-server – SQLAgentReaderRole and SQL AD ID login case affecting ability to edit jobs

Securitysql serversql-server-2008ssms

I have AD ID users on a SQL 2008 server that can't edit their own jobs. They can create them, but in order to edit them, they have to delete them and then re-add them. The issue is explained here: http://blogs.msdn.com/b/sqlserverfaq/archive/2009/06/08/owner-of-the-job-will-not-be-able-to-modify-edit-the-job-in-sql-server.aspx

I did further research and figured out in my particular case it's because I added AD ID users using the CREATE LOGIN [domain\user] syntax. Using that method, the case of the AD ID is what you specify in the brackets.

Is there anyway to create users with the correct Windows AD ID cases so that I'm not having to figure it out then modify the logins for each AD ID user?

Furthermore, I was using this script to get the correct casing for a particular user:

execute as login = 'usa\johndoe'
SELECT SUSER_SNAME()
revert;

But it was returning all lowercase for me, yet all uppercase when I asked the user to run SELECT SUSER_SNAME() and give me the result. The correct is all uppercase (well, in order for that user to be able to edit jobs).

I'm at a loss and very frustrated.

—
I've found the best (only?) way to get the correct case is using the SQL interface to add a user, clicking search, typing the AD ID, then clicking check names. For the example above, it returns all uppercase, despite that script example returning all lowercase.

Best Answer

Is there anyway to create users with the correct Windows AD ID cases so that I'm not having to figure it out then modify the logins for each AD ID user?

Built in, you already found it -- use the directory searching interface in the GUI. Alternatively, you could use SQL CLR (as mentioned), or write a PowerShell script. You could certainly write a routine to clean up an instance using either method.

Note that the GUI rewrites the domain name, even if it's incorrectly cased when entered, but not the login name itself. (You can see this by manually typing something in, then hitting the Script button.)

But it was returning all lowercase for me, yet all uppercase when I asked the user to run SELECT SUSER_SNAME() and give me the result. The correct is all uppercase (...).

Under impersonation, SUSER_SNAME() returns the principal name as entered. If you try the test again with a different casing, it will return the exact name you typed in. The only reason the impersonation works using an incorrectly-cased name is because the Windows collation says the comparison of the two is a match.

Not under impersonation, there are two cases: (a) group login: the principal name is returned from what Windows supplies, which is always correctly-cased, and (b) individual login: the principal name is returned from what is entered for the name in SQL Server (it doesn't go out to Windows to double-check).

Now, having said all that, try as I might, I cannot replicate this problem using 2008 R2 management tools. If you're still running 2008 (non-R2) management tools, one option would be to try upgrading the tools, because this appears to be a bug in Management Studio itself.

Related Solutions

Sql-server – How to give the least permissions for CRUD-managing and running SQLServerAgent jobs as well as Maintenance Plans

On the maintenance plans

I typically let my maintenance run as the agent account and I normally keep them owned by SA so when I quit or became a consultant things still run. When I help a client with maintenance/management I do the same thing. You see to run maintenance you need more than just CRUD permissions. You are doing backups (backup operator), index rebuild and statistics updates (DDL Admin, Alter or DBO), potentially error log recycles (not sure here perhaps setupadmin, definitely SA), etc.

For maintenance, it is a process you own, you setup and you trust. Like a monitoring tool, I see no issue with maintenance running as sysadmin.

I would also suggest you check out Ola Hallengren's maintenance solution. Maintenance plans are okay and serve a purpose, but he's put a lot of work into his maintenance solution and it works well.

On:

What does guarantee that non-sysadmin role user as owner of a job would have sufficient permissions to run an SQL Server Agent job successfully?

I believe what the documentation author is getting at there is likely the problem your first question deals with. If you make "Domain\JoeBlow" the owner of the job, and that user is just has the correct least privilege for, say, an accounting user, then that login would certainly not have sufficient permissions to do what the job needs to do - the job fails in that case.

Sql-server – How to update commands in Ola Hallengren’s SQL Server jobs

You can update steps without completely recreating everything and without pointing and clicking yourself to death in the UI. You can just write manual queries directly against msdb.dbo.sysjobsteps (start with SELECTs that issue the REPLACE() calls, then when you're happy, change it to UPDATE).

SELECT REPLACE(js.command, N'foo', N'bar') 
  FROM msdb.dbo.sysjobsteps AS js
  INNER JOIN msdb.dbo.sysjobs AS j
  ON js.job_id = j.job_id
  WHERE j.name = N'name of the job';

When you're satisfied it's correct, only the first couple of lines change:

UPDATE js
  SET command = REPLACE(js.command, N'foo', N'bar') 
  FROM msdb.dbo.sysjobsteps AS js
  INNER JOIN msdb.dbo.sysjobs AS j
  ON js.job_id = j.job_id
  WHERE j.name = N'name of the job';

It's tough to get more automated than that (e.g. update more than one job at once), since the string you want to replace might not be exactly the same in all job steps, and you might get false positives, too.

Best Answer

Related Solutions

Sql-server – How to give the least permissions for CRUD-managing and running SQLServerAgent jobs as well as Maintenance Plans

Sql-server – How to update commands in Ola Hallengren’s SQL Server jobs

Related Question