Identifying Databases Not Meeting Backup Service Level Agreements (SLAs)

A quick way to find out if your backups are working as you expect

One of the questions I always ask database administrator interviewees is “What is the number one job of a DBA?”.  I feel the answer says a lot about how this possible candidate thinks, and on another level if they read the job description or not.  What I hope to hear is “Ensure recoverability in the event of a disaster” or something along those lines.  If it wasn’t obvious, I feel strongly that recoverability of your database(s) is priority number one.

Because there is an ever-present need to ensure your backups are current and meeting your establish Service Level Agreement (SLA) with the business, it’s probably a good idea to have a handy query that will get this for you.  I also like to operate on the notion that “No News is Good News”, so the output of this report shows only databases failing the SLA.

When Builtin Database Roles Just Don’t Make the Grade

How to create a DDLAdmin-ish database role

A client of mine had a request to create a database role that allowed users to Create, Modify, and Drop Stored Procedures and Views.  Sounds simple enough, right?  Right… (the ellipsis implies sarcasm here).  Part of this request was that no other schema changes should be allowed.  Luckily the users requiring these permissions are developers and not DBAs.  They don’t really care about triggers, synonyms, etc so I find my job a little easier.  The only major objects I need to ensure they can’t touch are the tables; basically everything else is fair-game.

Truncating Tables Without Elevated Rights

A common request from users is to receive “Truncate Table Rights” within a database.  Because a truncate statement is a DDL command, this isn’t nearly as easy to fulfill as most people would expect.  Faced with this challenge, a DBA might even find themselves choosing between elevating the user’s rights (e.g. including them into the db_ddladmin or db_owner role) or being a bad guy and flat-out denying the request.  Neither option is a win for both parties, so thankfully (in SQL 2005 and later) there is another solution.