Utility function: dbo.JobName(@job_id)

As often as I have to pull a job’s name based on its job_id in the MSDB system tables, I’m truly stunned that I’ve never done this before:

CREATE FUNCTION dbo.JobName
(
@job_id UNIQUEIDENTIFIER
)
/* A little utility function to return a job name based on the job_id.
No warranty or guarantee implied. Use at your own risk. And don’t take wooden nickles.
http://www.MidnightDBA.com/Jen
*/
RETURNS sysname
AS
BEGIN
DECLARE @name sysname;
SELECT @name = name
FROM msdb..sysjobs
WHERE job_id = @job_id;
RETURN @name;
END;
GO

So yeah, now it takes fewer keystrokes to get the job name. I used to:

SELECT *
FROM msdb..sysjobschedules
WHERE job_id IN ( SELECT job_id
FROM msdb..sysjobs
WHERE name LIKE ‘CollectorDBFilePropertiesGet-%’ );

 

But now I:

SELECT *
FROM msdb..sysjobschedules
WHERE dbo.JobName(job_id) LIKE ‘CollectorDBFilePropertiesGet-%’ ;

Good lord. I guess it’s just a longstanding superstition about putting user objects in system databases. But enough is enough!

Happy days,
Jen McCown
http://www.MidnightDBA.com/Jen

Catch The Enterprise Scripting Workshop in Orange County, Baton Rouge, Albany, or Atlanta this year!

Explaining all the things
Explaining all the things

Sean and I have put together our bazillion (read: like 30 combined) years of SQL Server DBA-ing into one big, shiny pre-conference session that’s coming at you in multiple states of the union!

The Enterprise Scripting Workshop. What on earth is that about, anyway?  The short version is that we teach you the philosophy of managing a SQL shop, not Server By Agonizing Server (SBAS), but with a true enterprise mindset. And no, you do not have to know Powershell before you attend, but it can certainly help.

Our Chicago class this year rated the session at an average 9.4 out of 10! Just a few of the feedback comments from attendees:

I really enjoyed the day, gave me some good perspective…

Great session. I was afraid it was going to be just a bunch of scripting with various languages …It was so much more!

Nice answering of questions with reasons not just one-sided opinions.

Good team work! Hope to attend your sessions in future.

It’s a fun class, and Sean and I really enjoy it too!

Abstract

The database administrator (DBA) life can be frustrating: You rarely have time to innovate because the same tasks fill up your time day after day. Your users are unhappy about how long it takes to resolve “simple” tickets. You need to put big items on hold to manage special requests. As careful as you are, mistakes creep in the busier you get.

In the pre-conference workshop, learn how to develop enterprise scripts with a huge range of uses. A good set of reusable scripts can reduce task time from hours or days to just a few minutes, and eliminate mistakes from your environment.

  • Enterprise philosophy: Tackle simple tasks with the whole environment in mind.
  • Single data store: Define the benefits and uses of a single central database for common-use data and metadata.
  • Choice of tools: Choose the best tool (e.g., PowerShell, T-SQL, SSIS) for the job.
  • Environment ground work: Prepare your environment for enterprise scripting.
  • Real-world scripts: Work through dozens of enterprise scripting issues (e.g., alerting, error handling, multiple SQL versions) as you develop a real enterprise script in class

This session is for DBAs with a basic understanding of PowerShell. It’s for anyone who touches backups or security, maintains databases, troubleshoots performance, monitors disk space, or any of a hundred other DBA tasks. Enterprise scripting is for anyone who has more tasks than time.

Agenda

The morning will be spent largely in discussion and lecture, so the attendee has solid scripting principles to build upon. The afternoon session will be spent largely in actual enterprise script development.

  • Morning
    • Introducing your enterprise
    • Enterprise philosophy
    • The ideal SQL shop
    • Elements of enterprise scripts
  • Afternoon:
    • T-SQL and regular expressions
    • Developing the Script
    • Applying RegEx

Resources

If you’re dying to get started without us, take a look at a few related resources:

Happy days, and see you soon!
Jen McCown
http://www.MidnightDBA.com/Jen

Well that’s just neat: Row Level Security, new in SQL Server 2016

SQL Server 2016 offers Row Level Security, which sounds awfully cool. It’s apparently been available in Azure for a while, but the on-prem version is getting it, now.

Securing Rows

Okay, so: row level security (or RLS as the MSDN article so groovily puts it). What’s that, then? In short, it’s a method of securing the rows in a table such users only get to see the data they’re entitled to.  More officially:

Row-Level Security enables customers to control access to rows in a database table based on the characteristics of the user executing a query (e.g., group membership or execution context).

I’ve needed this exact thing in multiple shops over the decades, and of course you end up either trying to control access on the front end, or doing some rather fancy (and hard to maintain and scale) footwork on the back end, or – most often – just keeping different databases for different groups of users. Oh yes, it’s just as fun as it sounds.

Now though, we can just create a table valued function – this holds the logic of who gets to see what, and why – and create a security policy on the table, using that function as a filter predicate.  To control who gets to insert what data, create another TVF and use that as a block predicate. (No, Company2, you can’t insert data for Company10. Get real.)

Check out the code examples on MSDN for a nice few scenarios.

Where it helps in real life

This really is great.  This means that the one client* – call them Client X – that keeps 100 separate databases for each of their clients, can now keep just the one database for all of them, if they so choose. Right now, Client X has a ton of common data – product lists and so forth – in a common database, and individual client data – like sales info – in the individual DBs.  What they’ve ended up with is a system of cross-database queries (to combine individual data with common data), cross-database aggregate views (that they have to update each time they add a new client/DB!), and just a big old mess.

There are other examples, but I’ll spare you. If you’ve been in the business long enough, you’ve seen plenty of scenarios like this. But, back to the point.

When Client X upgrades to SQL Server 2016, they can combine everything into one database – one set of objects to maintain, one set of code, no cross database queries! – and implement RLS fairly easily based on their client IDs. It’ll take a bit of rework, but it’s also going to help their future development and troubleshooting efforts 1,000-fold.

 

*I’m combining and fictionalizing this a little – and only a little – to protect client identity, NDA, yadda yadda yadda. It’s still a valid scenario, and very close to the real life situation. 

Finally

There’s a good deal more to RLS, of course.  And MSDN has done pretty well at outlining the behavior, possibilities, and even some really interesting security risks. Go on, read up.

A caveat, after all of this: I tend to get really excited about new** features (well, some of them), and I don’t always see the potential downside. So I’ll be looking forward to reading commentary from some of my more skeptical fellows.

Still though, row level security looks pretty good.

 

**Yes I know it’s not new-new, it’s been in Azure for a while, and other DB products have it. But I’m still mostly an on-prem DBA – when I am a DBA – and 2016 will be my first chance to play around with the feature.

 

Happy days,
Jen
http://www.MidnightDBA.com/Jen

Real news, real tech.