September 10, 2019 by Kenneth Fisher
It’s fantasy football time again?
Oh, fantasy feature?
Oh, yea, that makes a lot more sense for #tsql2sday.
It’s a blog party currently being run by Steve Jones (b/t) where each month a different host comes up with a topic that we can all blog on. Joining in is a great way to get started blogging if you are having a rough time coming up with topics and if you’ve been at it a while and want to host I’m sure Steve will be happy to check the schedule.
Which brings us back to this month’s host! Kevin Chant (b/t)! Kevin wants us to talk about our fantasy SQL features and I have to admit, I don’t really have any. Not new anyway. I have a few I wouldn’t mind updated though.
Very quickly I’d love it if sp_helpindex displayed more information about the indexes. Included columns and filters as an example. Now, I have no hope this is ever going to happen but it’s become of a bit of a pet peeve (peeves make great pets don’t they?) for me. I was basically told that because custom scripts like sp_blitzindex and sp_SQLskills_helpindex exist (both are amazing tools with great information) there is no reason for the base tool to include this very necessary information. Enough said.
Better SQL Agent job permissions
If you’ve worked with SQL Agent jobs much you’ll be aware that the majority of the permissions (to create, view and edit) them are found in the three roles SQLAgentUserRole, SQLAgentReaderRole, and SQLAgentOperatorRole which in spite of what the linked post says are not fixed (as of 2017 I can still change their permissions anyway). I’d love to see these permissions changed rather dramatically. How about permissions like JOB EXECUTE, JOB READ and JOB WRITE that can be granted at the database (msdb) level and at the job level. I’ve also always thought that it would be nice if an AD group could own a job (allowing individuals that are members of group to edit it, for example).
I have more hope that this one will happen, although maybe not in the way that I’m envisioning it. Which is more than fine. The people who work on SQL Server are a) very talented and b) have constraints that I don’t know about. But security is a big deal for Microsoft so keep your fingers crossed!