Every big enterprise product has them. I just happen to pick on SQL because it’s my area of expertise.
This week’s topic was triggered by an amazing quote from Adam Machanic (b|t) in the sqlcommunity Slack group, where he was trying to help someone troubleshoot a slow linked-server query:
That default, I consider to have been checked into the SQL Server codebase directly by Satan.
He’s referring, in this case, to the default option of remote proc transaction promotion, or “Enable Promotion of Distributed Transactions for RPC” in the GUI, which is set to Trueby default. Admittedly, linked-servers are a dicey area of the technology and not everybody needs them, but when you do need to deal with them, there are some caveats to consider and a few potholes to avoid.
I won’t go into gory details, but the mile-high gist of it is that you should probably change a couple of the default linked-server settings when you’re going from MSSQL to MSSQL (which most of the time, you are): rpc and rpc out both to true, and the above dude to false. The reasons behind that are subtle, but it boils down to usage patterns: If you’re purely running basic SELECT statements against the remote server, you can leave this stuff alone; but if you want to do cool stuff like exec MyLinkedServer.RemoteDB.sys.sp_executesql '--some awesome dynamic-sql', you’ll want to change these. (That last bit comes in especially handy if you’re building a dynamic query string, then running it against the remote server to bring in the results to a #temptable to further massage/mangle/munge said data.)
Even though you probably really shouldn’t be doing that in the database (that’s what web server farms are for!).
So, what are some other “stupid defaults” in SQL Server?
Every big enterprise product has them. I just happen to pick on SQL because it’s my area of expertise. And it’s not even just “defaults”; there are some options which shouldn’t even be a thing — they should be completely and irrevocably in love with Edward removed from the product. Yet, because the RDBMS tech space is infamously conservative and slow-to-change (the accepted euphemism is “mature“), these options and defaults have remained in the product despite our best attempts to convince MS that they’re heinous.
1. Parallelism settings (server/instance level)
Your servers have many-core CPUs, right? And you want SQL to utilize those cores to the best of its ability, distributing the many users’ workloads fairly amongst them, yes? Damn right, you paid $3k or more per core in freaking licensing costs! “OK”, says SQL Server, “I’ll use all available CPUs for any query with a ‘cost’ over ‘5’“. (To give context here, in case you’re not aware, ‘5’ is a LOW number; most OLTP workload queries are in the double to triple digits). “But wait!”, you protest, “I have more than 1 user, obviously, and I don’t want their horrible queries bringing all CPUs to their knees and forcing the 50 other user queries to wait their turn!”
Hardly seems fair, no? But those are the defaults, have been for over a decade, despite how much hardware has evolved since. Just for completeness, I am talking about the following 2 options, which you can view by right-clicking on the instance in SSMS, or by running sys.sp_configure. They are max degree of parallelism and cost threshold for parallelism, respectively (# cores to use, and how expensive the query should be to “go parallel” — as opposed to serial/single-threaded, which is perfectly fine for those itty-bitty-teeny-tiny queries that are so quick that 1 CPU core is more than enough to handle them). We commonly abbreviate the former as maxDOP; the latter is less commonly abbreviated, but because I’m a sucker for acronyms, I like to call it CTFP, or maybe costFP if I’m feeling verbose.
And if you have somewhere between 9 and 15 CPU cores, don’t ask me, because you’re running some.. interestinghardware. Figure it out yourself, and get that gun out of my face!
OK, I know 12 cores is probably not an unreasonable config, so.. extrapolating my logic above, divide by.. 3? Yeah let’s go with that.
2. Auto Close (database option)
It’s at the top of the list in the GUI under Database Properties -> Options. Yet nobody knows why you would ever enable it. I once heard a community member theorize that it might have been put into the product back when it was considered a viable “local persistence option for Windows CE/Mobile apps” and it would help w/ resource management on said mobile device by freeing up resources when the DB wasn’t in-use. Well, we all know how well that product line did in the market (hint: poorly). There are so manybetteroptions for localized data stores in mobile dev, MS isn’t even a blip in the conversation. (Again, talking local data persistence.)
If we’re talking cloud, MS is a big part of that conversation — Azure is amazing, and a solid competitor to AWS & GCP.
Anyway, if you ever find a SQL DB with the auto_close option enabled, find the person responsible, and slap them with a trout.
3. Server Max Memory
Last one for today. This is something that’s not completely heinous, but could use a LOT more built-in intelligence during the installation process so that DBAs & SysAdmins didn’t need to think about it so much. SQL Server will, by default, sets its max-memory to some-odd-billion-MBs (technically it’s the max value of a 32-bit int, which the more geeky among you have probably memorized), which is of course some-odd-million-GBs, which is more than even the most bleeding-edge servers have to date. Which is fine in theory — you paid a crap-ton of money for this system, it might as well use up all the RAM that it can to perform to its potential, right?
Right. Until you realize that “Oh wait, it’s running inside an OS” (whether that’s Windows or Linux, thanks to 2016 & 2017 product versions) — that that OS needs some RAM too, to keep itself running! (Cue the Linux zealots with their “it’s so much more lightweight than Windoze, haha!!1” — yeah, well you still need some memory space, don’a ya?)
Here’s what I’d like to see, in my ideal world. During SQL Server installation, it would detect how much RAM is on the system, subtract about 10% or 4-8 GB, leave that for the OS, and use the resulting number as its limit. Boom, done, nobody has to think about configuring it and checking off another checkbox on their setup checklist.
But noooo… The vaunted MSSQL engineers can built all sorts of amazing things into the product like QueryStore, Adaptive Query Processing, and The Artist Formerly Known as Hekaton, but heaven forbid we get a little more intelligence in the installer. It got a lot better with 2016 when it let you configure tempDB reasonably correctly (multiple files, different locations, etc), but there’s still a LOT that could use some lurv.
Do you have a favorite “stupid default” or “horrible setting” related to SQL Server or any other technology that you work with? Share in the comments!
as a developer, DBA, or hybrid “DbDev”, you’re often tasked with writing or improving the stored procedures which house that complex logic. And that’s my topic today: being clean about your SQL code.
Get it? It’s just too punny! … Ok I’m done.
The Coding Blocks guys did a series of episodes about the perennial favorite Clean Code book. If you haven’t subscribed to their podcast…
And it’s a great book, no doubt. But those guidelines for application code are not 100% directly applicable to database code.
Let’s back up a second. Why? That sounds about counter-intuitive, no? Ok, more context. See, the traditional (“legacy”?) app consists of about 3 layers, the bottom one being the database. And it’s usually relational, and is usually responsible for far more than simple data access and persistence. Read: complex business rules and process logic. Data flow, not just getters and setters.
So that means, as a developer, DBA, or hybrid “DbDev”, you’re often tasked with writing or improving the stored procedures which house that complex logic. And that’s my topic today: being clean about your SQL code.
Part 1: Comments
There’s a fairly famous quote from the book about comments:
Comments are always failures.
He’s using hyperbole, but for a purpose. While his views on comments may be extreme, most programmers tend to realize the core essence of that chapter, which is that comments only serve to express something in plain English that the code has failed to express clearly enough to be easily and immediately understood.
With SQL scripts, and in particular with stored-procedures, I’m taking a somewhat opposite stance:
Comments are always appreciated, even if they’re potentially outdated or inaccurate.
There are two types of comments in SQL, the --inline and the /* block */. Different people have their preferred flavors of block — sometimes it’s just several lines prefaced with the double-dash --. And that’s fine, whatever floats your comment-boat.
In particular, I always encourage a comment block at the top of ever stored-proc & other user-defined programmable objects (function, types, etc). Just a small example for illustration:
Description: Gets users who have not logged in since the given date.
Consumers: MyCoolAppName, MyReportServer
2015-05-15, Nate: removed archive (never used after archive-date)
2017-06-07, Nate: fixed formatting for blog post
CREATE PROCEDURE GetUsersNotLoggedInSince
--some clever stuff goes here...
“But wait”, you say, “what about source control?” Yes, all your programmable objects (and even, arguably, your reference data) should be in source control. There are tool-vendors aplenty to help you with that. But guess what? Budgets. Time & effort. Oh, did I mention, legacy legacy legacy? Yes, dear reader, the average business has years (decades) of organically evolved relational databases and processes. Are you the guy or gal to swoop in on your unicorn and seamlessly convert their entire data tier infrastructure to a beautiful DevOps pipeline with shiny rainbows and kittens for all? No? Okay then. Baby-steps.
Yes, my procs are in source control. It’s called “daily automated script-out-objects-to-files which are then committed to SVN”. It’s not built-in to SSMS. Which means that I, or another DBA, or a potential consultant, or a Dev who gets enlisted to help improve a proc that runs for hours when it should only take minutes, would be inconvenienced by the extra trip to a separate tool/system to fetch some change-history just for context. And really, that’s all this is for — CONTEXT. We like to know what it is we’re working on when we start to work on it, without having to traverse a change-tree or go bug 3 other people who “might” have touched it last. I’m not asking for a detailed log of every single time someone touched the thing; just give me the overview, the milestones and significant changes to functionality/features/scope so that I have a jump-off point for troubleshooting/testing/reasoning about it.
“But wait”, you say again, “shouldn’t your name be a sufficientdescription of what the proc does?” Sure, in theory. Until you have dependencies which need that name to stay the same even after an update or logic-change. Like reports. Or data-connected Excel workbooks. Which are used daily by managers, who will come yelling at you if their worksheets suddenly stop functioning.
Back to comments in general. The reason they’re helpful (besides documentation-headers for objects) is that they provide context and explain intent. Half the time, my job as a DBA is improving or fixing someone else’s code. Therefore, I want to see, in plain English, what it is they’re trying to accomplish, notes about attempts and failures, and the like. Yes, I could have a discussion with them. And I will. But if I’m working on it asynchronously and they’ve moved on to something else, or our hours are different, I want those little nuggets of context and intent to be right there in the script, because that’s where I’m working!
What about queries that get passed-down from the app to the DB? ORMs don’t support pre-pending a comment to their data calls, do they? I wish. Maybe some do, I haven’t researched it, but I know for sure that LINQ doesn’t. But then again, when I’m using a query-capture tool (like DMVs, Profiler, X-events, or a vendor monitoring tool), ORM queries are so painfully obvious in comparison to hand-crafted SQL that I can usually spot them from a mile away, and go bother the app-devs for some context & conversation. If you’re one of the poor unfortunate souls who still passes ad-hoc generated SQL statements down thru ODBC to your DB, then sure, a little comment won’t hurt anybody.
So do your DBAs a favor, comment your SQL code, at least in terms of programmable database objects and ad-hoc scripts. I promise, it’ll make them hate you less. It might even make you love yourself more, because 3 months down the road when you revisit that proc, and you need to remember what it was for and why you did it that way, you’ll see it right there in your very own writing! (OK, typing.)
Part 2: SRP, Encapsulation, and Abstraction
A bit of paraphrase of one of the book’s key points:
A reusable module (function, method) should do one thing, and do it well.
Also, the DRY principle:
Don’t repeat yourself.
When building SQL modules, we’re usually concerned with performance and accuracy, over abstraction and composability. Therefore, repeating oneself is not necessarily a bad thing, when done for the right reasons. Diligence is a big factor here — if there’s a non-trivial relationship between some entities that you’re repeating in several places, and you know that it could become a maintenance headache if that relationship’s definition has to change later, do as much as possible to mitigate the risk of dependency/consistency-loss. This can be documentation, comments, and/or building that relationship into a view.
The latter brings up an interesting topic, one which I had a lively discussion about with a colleague recently (he’s a developer, and a dang good one) — nested views. Because inevitably, the encapsulation of those relationships & business-rules into things like views or ITVF’s can and will lead to nesting those objects into other objects. And troubleshooting many-level-nested views is a particularly frustrating exercise; in fact they’re what some DBAs call one of the “deadly sins of SQL”. But there are perfectly valid reasons and uses for them, sometimes, and I really enjoyed the discussion thread we had on it, so I’ll have to expand on that in another post.
Anyway, I’m already getting long-winded and well over 1k words, so I’ll wrap it up for now, and continue this topic next week.
Look, I get it, audits and auditors are a necessary evil. But the sheer volume of crappy code, hack-y scripts, man-hours, and alcohol, dedicated to meeting the often arbitrary, sometimes ridiculous, and possibly even downright obscene requirements, of said auditors, is staggering. There are entire product suites dedicate to help you “report on” or “meet/pass” audits of various kinds. And thank god for those. Because without them, we’d all be slightly more insane than we already are.
I’m not crazy; my mother had me tested. And my rubber duck agrees.
Here’s an example.
In one of our main applications, at the database level (SQL), we’ve implemented poor-man’s data-change-tracking via table triggers. In a past audit, there was a “flag” on an unauthorized data change. As a result, an “exception report” was created, which basically produces a daily CSV dump of those changes and emails it to a few managers. Now, because the tracking table includes “who” as well as “when” & “what”, and there are certain privileged accounts that can be ignored for auditing, and because there are dozens of application users whose app-controlled changes are also logged in said tracking table (and need to be ignored), the “report” writer at the time decided it was a wonderful (read: terrible) idea to hard-code a list of usernames into the query that produces this data-dump.
That is horrible. Never do that!
This query, the job that scheduled it, the data that it produced, and the emails that it sent, were subsequently filed away and silently forgotten about. Until we did some environment cleanup and discovered “Oh look, here’s a thing that’s not doing anything” (because the hard-coded list of users was now woefully out of date), and the same managers who received the reports for years past, gave the OK to disable the job!
What could possibly go wrong?
Then the next audit season comes around. And guess who comes running back to the DBA having changed his mind? Why yes, that very same manager!
So I tell him what we need to do to fix it — remove the hard-coded list, replace it with an AD group or at least a look-up table that can be periodically updated with “current” users in the desired departments/teams. We do that, and the CSV dump goes from zero to huge. As in, too huge for email. So we try to pare it down. We get it to a manageable size. Then we get ready to deploy the changes and confirm the desired email recipients.
Boss: “Oh it doesn’t really matter, we just ignore the emails anyway… it’s just to satisfy an audit requirement.”
Me: “So why did we even bother fixing it?”
Boss: “Well, it was broken!”
I’d like the last hour of my life back please.
Here’s another fun one.
The ERP system has a rickety tack-on audit-trail system involving some SQL Agent Jobs and an obscenely large data repository for storing all the changes that the ERP system users make every hour of every day. Thankfully, somebody along the way was prescient enough to partition those storage tables using a partitioned view scheme. But apparently there were NOT smart enough to realize that the clustered index needs to be the datetime column. Yes, it was in the primary key, but that PK was not clustered (nor should it have been, since the actual unique keys were GUIDs, but this is a prime example of making the clustering key different and separate from the PK.
Obviously enough, in a date-driven partitioned view scheme, that datedoes need to be involved in the PK also. But again, the best clustered index is just that date, because A) it’s the partition divider, and B) it’s always your queries’ main filter-predicate (which is a fancy way of saying “the thing in your JOIN/WHERE clauses”).
Once again, audit season comes around, and someone needs those reports driven by these audit-trail repos. And guess what? They’re slow as molasses.
So we get to spend another few hours applying the correct clustered indexes, waiting for them to rebuild, testing the queries for the report, and finally running the report itself. Oh and don’t forget the down-time of the jobs, and having to “catch up” with all the changes that were made in the ERP system while we were working on all this.
Is it 5 o’clock yet?
Audits suck. But please, stop producing crappy code in response. It just makes the next guy/gal that comes after you even more frustrated than he/she already is with the whole process. Practice the “boy scout principle” — leave things at least a little better than you found them. And if you’re forced to produce a ridiculous “exception report” that nobody will ever read, spend as little time on it as possible while still making it less heinous than the last terribly designed monstrosity.
When a date range, or time period, is specified in SQL, it’s easiest, clearest, and most concise, to use a “greater-than-or-equal-to Period-Start, and less-than Next-Period-Start” logic. Mathematically speaking, we are defining the range as closed on the left, open on the right.
This is a bit rant-y, but… indulge me. I’ve been writing/refactoring a lot of old reporting queries. And, like most reports, they deal with dates and datetimes — as parameters, boundaries, or where/join predicates. I also got way too intense with a recent SSC post (Sql Server Central), which fueled the fire even more.
SQL Server is very good at handling temporal datatypes and calculations against them. We’ve got functions like dateadd, datediff, dateparts, datatypes datetime2 and datetimeoffset, date, time, etc. It supports all sorts of format conversions if you need to display them in various ways.
..even though that should be left to the presentation layer!
Here’s the issue. Well, there are several issues, but we only have time for a few.
Here’s the first problem
Report users don’t understand the “end of a time period” problem. I don’t have a good name for it; others might call it the “Day plus one” problem or the “Less than date” problem. What do I mean by this? Well, let’s back up a bit, to DBA Commandment #6, “Thou shalt not use between with datetimes.” In order to first understand the issue, we have to understand why this is a commandment.
When a date range, or time period, is specified in SQL, it’s easiest, clearest, and most concise, to specify it like so: @TheDate >= @StartOfPeriod and @TheDate < @StartOfNextPeriod. Mathematically speaking, we’re defining the range as “closed on the left, open on the right”. In other words, Min <= X < Max.
The reason we do this with datetimes is found right there in the name of the datatype — it has (or can have) a time component!
Let’s talk examples
Say you’d like to report on the month of March 2017. How do you determine if your data-points (stored as datetime or, hopefully, datetime2) are within that period, that month? Well sure, you could write where month(MyDateColumn) = 3 and year(myDateColumn) = 2017 …
NO. That is horrible, don’t do that.
It’s not SARGable and renders your index on that column useless. (You do have an index on it, don’t you? No? Make one!) Okay, let’s stick with something SARGable. How about MyDateColumn between '20170301' and '2017-03-31T23:59:55.999'? (You did read this post about using culture-neutral datetime literals right?) But wait! If your data is a datetime, it’s not actually that precise — your literal gets rounded up to 20170401 and you’re now including dates from April 1st (at midnight)!
Many developers and report-writers assume that the values in their data will never be within the typical “1 second before midnight” or “1/300th of a second before midnight” escape window of your “3/31/2017 23:59:59.997” bounding value. But can you guarantee that? Didn’t think so. Worse, if you use the .999 fraction as given in the 2nd example, you’re either “more” or “less” correct, and nobody can actually tell you which way that pendulum swings because it depends on the statistical likelihood of your data having actual literal “midnight” values vs. realistic (millisecond-y, aka “continuous”) values. Sure, if you’re storing just a date, these things become a lot less complicated and more predictable.
But then why aren’t you storing it as an actual date, not a datetime!?
So what’s the right answer?
As I said, “greater than or equal to ‘Start’, and less than ‘End'”, where ‘End’ is the day after the end of the period, at midnight (no later!). Hence, MyDateColumn >= '20170301' and MyDateColumn < '20170401'. Simple, yes?
But wait, there’s more!
I mentioned “date-pickers” in the title. When it comes to UX, date-pickers are a sore subject, and rightly so — it’s difficult to truly “get it right”. On a “desktop-ish” device (i.e. something with a keyboard), it may be easiest on the user to give them a simple text-box which can handle various formats and interpret them intelligently — this is what SSRS does. But on mobile devices, you often see those “spinner” controls, which is a pain in the arse when you have to select, say, your birth date and the “Year” spinner starts at 2017. #StopIt
I mean, I’m not that old, but spinning thru a few decades is still slower than just typing 4 digits on my keyboard — especially if your input-box is smart enough to flip my keyboard into “numeric only” mode.
Another seemingly popular date-picker UX is the “calendar control”. Oh gawd. It’s horrible! Clicking thru pages and pages of months to find and click (tap?) on an itty bitty day box, only to realize “Oh crap, that was the wrong year… ok let me go back.. click, click, tap..” ad-nauseum.
The point here is, use the type of date-picker that’s right for the context. If it’s meant to be a date within a few days/weeks of today, past/future — OK, spinner or calendar is probably fine. If it’s a birth date or something that could reasonably be several years in the past or future, just give me a damn box. (Heck, I’ll take a series of 3 boxes, M/D/Y or Y/M/D, as long as they’re labeled and don’t break when I omit the leading-zero from a single-digit month #!) If there’s extra pre-validation logic that “blocks out” certain dates (think bill-payer calendars or Disneyland annual-pass blackout-days), that probably needs to be a calendar too.
..just make sure it’s responsive on a mobile device.
And in all cases, pass that “ending date” to your SQL queries in a consistent, logical, sensible manner. For reporting, where the smallest increment of a period is 1 day, that probably means automagically “adding 1 day” to their given end-date, because the end-user tends to think in those terms. I.e. if I say “show me my bank activity from 1/1/2017 to 1/31/2017”, I really mean “through theend of the month“, i.e. the end of the day of 1/31. So your query is going to end up wanting the end-date parameter to be 2/1/2017, because it’s using the correct & consistent “greater than or equal to start, and less than start-of-next” logic.
I know it’s not easy to explain to business folks, and it’s not easy to implement correctly. But it’s important. The >= & < logic is clear, concise, and can be used consistently regardless of underlying datatype. You just need to adjust your presentation layer (whether that’s SSRS parameters or a .NET date-picker) to convey their intent to the user, whether that’s “show/enter the last day of the month, but translate to the next day to feed to the query/proc.”, or “make them enter the next-day (day after the end of the month/period) and understand the ‘less than’ logic.” I’m more inclined to the first, but it depends on your audience.
MDX is not SQL. It may look like it has SELECT/FROM/WHERE clauses, but god help you if you start drawing parallels to your standard TSQL query.
This is an exercise I had to go through recently, because A) the reports in question were deployed in SSRS but used an SSAS backing, i.e. cubes, and the source queries (MDX) were not stored in source-control, and B) I don’t write MDX queries.
Run Profiler or XEvents against the SSAS server
set to capture “Query Begin” events only, with “Event Subtype = 0” (for MDX query)
optionally, set filter on NTUserName to the dedicated SSRS account (if you have it set up that way)
Run the SSRS report(s) that you want to dive into
For each event in the Trace, copy-paste the MDX query to a new MDX editor window
SSRS parameter substitution happens via some XML at the bottom; but in MDX, the parameters are standard @params like in T-SQL. So we need to manually substitute our parameter values.
2 blocks of XML: the “Parameters”, and the “PropertyList” — delete the latter.
In the former, text-replace & for simply & .
Side-bar: You’ll notice that the MDX parameters are usually inside STRTOMEMBER() or STRTOSET(), which are built-in MDX functions that do exactly what they sound like — parse a string into a dimension’s attribute’s member or set of members. That’s why they’ll usually have at least 3 .‘s (dots) — Dimension.Attribute.&MemberValue, for example. I’m grossly oversimplifying that because it’s beyond the scope of this post, but read the docs if you need more gritty details.
For each parameter node:
Copy/cut the <Value> node content (I like to ‘cut’ because it helps me keep track of which ones I’ve done already)
Find-and-Replace @ParameterName with that Value node’s content, surrounded in single-quotes
Example: we have parameter @ReportDate (in MDX), corresponding to <Parameter><Name>ReportDate</Name> in XML, with <Value xsi:type="xsd:string">[Some Dimension].[Some Attribute].[Some sub-attribute].&[2017-05-01T00:00:00]</Value> — where that last bit is a standard SQL datetime literal.
So you replace @ReportDate with '[Some Dimension].[Some Attribute].[Some sub-attribute].&[2017-05-01T00:00:00]' .
Delete the XML block.
Boom, now you have a valid MDX query that you can run and view results.
Why do this? Well, it can help you learn MDX from a working example, instead of from super-basic dummy examples. That’s not always a good learning style — you should still learn the fundamentals of MDX and why it’s so very different from SQL. Especially if you’ll be responsible for writing and maintaining more than a few MDX queries. But, in a pinch, if you need to start somewhere, and possibly all that the MDX / overlaying report needs is a slight tweak, this may be enough to get you going.
Profiler can still be a useful tool, despite some people’s attempts to kill it.
MDX is not SQL. It may look like it has select, from, and where clauses, but god help you if you start drawing parallels to your standard TSQL query.
SSRS does parameter-passing in an odd way.
SSAS & MDX are fascinating and I need to learn more about them!
A typical part of a DBA’s work-week might involve the occasional DB user-role-membership management, so I hope this helps the lone-wolf DBAs out there and/or the developers who need to know what to ask for…
Just a brief post on adding/removing users (database level users) to/from roles (database level roles). It’s relevant because several shops are still stuck supporting at least a few 2008 (or hopefully, 2008R2) instances, and there is a key difference between those and newer (2012 & up) versions in the “preferred” method of doing this security task.
There are reams of documentation and books and articles written about SQL security in general. That is beyond the scope of this post (and indeed, beyond the scope of any single blog, unless you’re an SME on the subject!). But a typical part of a DBA’s work-week might involve the occasional DB user-role-membership management, so I hope this helps the lone-wolf DBAs out there and/or the developers who need to know what to ask for, when they’re planning/deploying a new app against their SQL DB(s).
The “old” method involves calling system stored-procedures, sp_addrolemember and sp_droprolemember, in which you pass the role-name and username. The “new” method, supported starting with SQL 2012, is to use the command-phrases ALTER ROLE [role] ADD MEMBER [user], and ALTER ROLE [role] DROP MEMBER [user].
The latter is more ‘standard‘, while the former is more ‘Microsoft-y‘. I couldn’t easily find whether it’s part of the official ANSI standard or not… that’s an exercise for the reader. What I find very interesting is that Azure’s data warehouse offerings require the old method. Of course, hopefully in a DW setting you’re not messing with security nearly as much as a typical OLTP system, but… yeah.
Does that mean those Azure services are built on top of older SQL engine versions? Possibly. MSFT isn’t too open about the deep internals of such tech, but neither is any other cloud vendor, so we can’t really ask them such a question and expect anything more than a blank-stare. But it is curious, no?
Syntax examples: Let’s add the user foo to the database Bard, in the db_datareader built-in role. Then we’ll remove him. (Or her, I guess; “foo” is a pretty gender-neutral name.) Creating said user is easy, so I’ll start with that, and it’s the same in all supported versions. You need a server-level login to link it to; if you don’t have one, I’ll show you how to create it first.
Create server-level login:
--preferably, you create a login for an existing AD/Windows account:
CREATE LOGIN [yourdomain\foo] FROM WINDOWS;
--or, you can just create a SQL login (not connected to domain/Windows/ActiveDirectory; also less secure, as discussed here and here)
CREATE LOGIN [foo] WITH PASSWORD = 'foobar';
Create database-level user:
--if you made the domain/Windows login:
CREATE USER [foo] FOR LOGIN [yourdomain\foo];
--or, if you just made the SQL login:
CREATE USER [foo] FOR LOGIN [foo];
ALTER ROLE db_datareader ADD MEMBER [foo];
Check (see above)
Remove user from role:
ALTER ROLE db_datareader DROP MEMBER [foo];
Notice that, because the “old way” is simply executing sys-sp’s, we can actually run it from any database context. Whereas the “new way” requires you to connect to the database in question.
Note: I am in no way shape or form responsible for you screwing up your database or SQL instance, nor for you getting yelled at by your DBA or security admin or any other form of verbal assault you may incur as a result of running these commands. But since you need server-admin & database-owner equivalent permissions anyway, you’re probably one of those people already, so you’ll just end up yelling at yourself.
Cleanup (just so you don’t muddy your instance/DB up with a silly example user):
DROP USER [foo];
DROP LOGIN [foo];
If you have any questions, feel free to reach out to me!
This is a story. And it’s longer than my usual post, so get comfy. I may be stretching my own rules, but I swear, I’ll tie it back to databases… somehow! Let’s get started.
Back in December 2016, I was in an accident in my 2011 Mazda 3. Ironically, I was driving home from filling up with gas, plus I’d just had some maintenance done the month before. These things are ironic because the car was a total loss. “Totaled”, in layman’s terms. It means the damage was such that the insurance company would rather pay off the market value of the car, than pay for the repairs. Or, put another way, it means that the cost of repairs would be within nominal range of the vehicle’s value. Short version, I’m not getting the car back. Oh, and that gas fill-up and mechanic bill? Money down the drain.
FYI, I was just fine – so the car and its safety-system did its job, protecting me from harm. Safety is an important part of choosing a car, kids… remember that.
Anyway, the car gets towed off to a local yard, I have a day or so to collect my possessions from it, and then they tow it somewhere else to the “salvage yard”, where it becomes somebody else’s property and problem. Insurance sends me the check for the value of the car. I was actually worried that it wouldn’t be enough to cover the loan balance, because I was still making payments AND I’d refinanced midway thru the original loan term, lowering the interest rate and payment but also extending the term. But, thankfully, the car’s value was above the loan’s balance, so I was able to fully pay it off and still pocket some cash.
Unfortunately, I didn’t have “loss of use coverage” on the policy – meaning, no free rental car. So I pay for it, for a little while. Fortunately, around the same time, my parents were getting ready to dump their old 2000 Honda Accord for a newer one, so we started talking and they offered to us as a gift. (Did I ever mention how awesome my parents are? They are!) Excellent.
Actually, this is the same car that I used to drive around in high school, so it’s got some memories.
Including, of all things, my very first accident! You’ll see why this is ironic in a few paragraphs.
Now, this car is what we call a “beater”. It’s 17 years old, it’s been through the wringer, it’s got 190k miles on it; but hey, it’s a freakin’ Honda. It’ll last another 50k at least, if maintained properly. And it has been – faithful oil changes, scheduled maintenance and beyond. But it’s not the safest vehicle on the road; the e-brake light comes on sporadically, and the airbag warning light is always on, so we don’t actually know if the airbags (particularly the passenger side) will work. So we need to start shopping for a new vehicle, at least for the wife.
I won’t go into car-shopping here, it’s a pain the arse unless you use courtesy buying services like those offered thru your credit-union or your some kind of “club membership” or whatever. Long story short, we got a 2017 Hyundai Elantra, in black, not brand-new but used with only 3k miles on it (apparently they had buyer’s remorse).
I will digress just for a second about black cars. They look pretty slick, even though they do show dirt a bit more than gray/silver (which is what the Mazda was). But you know what makes them look super-duper slick? Those “legacy” CA gold-on-black license plates. I convinced myself that I had to get those. Until I went to the DMV and found out that they’re $40 initially plus an extra $50/year on your registration fees. Jesus H… I get that they need to make money, but that’s ridiculous. Srsly. Ding the people that want those silly vanity plates, because I understand it adds a lot of processing/tracking overhead and makes the data (see? I told you I’d tie it back!) more complex. But this is the same old metal made by the same old prison inmates with a different coat of paint. Don’t pretend it actually costs anything extra for you to make them and pass them out. Anyway. Back to the story.
So we get the car, the wife’s driving it home. She’s heat-sensitive, and it’s been a pretty long, warm day. She ends up passing out for a second and veering off the road to the right shoulder, which is a small dirt embankment into some bushes and trees. Fortunately enough, she realizes what is happening and she’s able to bring the car to a gentle stop without actually hitting anything. So the car’s only real damage is some scrapes & bruises on the front end, a bit of scratching on the sides, and some dings to the under-carriage-panel. Now, me being the savvy consumer that I am, I’ve already added it to our insurance policy and added rental coverage. The new car goes into the shop before we’ve even had it a day, but we get a free rental while it’s there. (Ford Fusion – I like it alright, but the wife hates it; she has a bit of an anti-Ford bias.) Insurance covers about 1.5k damage for the bodywork, it gets done, we get it back in less than 2 weeks. Yay.
Alright, here’s where it gets fun interesting.
That was all back in December/January.
March rolls around, and I’m driving the Honda home from work. There’s a sudden pile-up of stopping cars in my lane and I can’t stop in time, so I run into the SUV in front of me. Fairly low speed, nobody panics, we pull over and start exchanging info and pictures. Now, my bumper is nearly detached, and my hood is quite scrunched in at the point of impact. This is because I hit his tow hitch, which stuck out from the rest of his rear body quite a bit. So even though he literally has a 1-inch scratch on his bumper, I’m looking at significant damage. But it seems mostly superficial, so I figure, well, I might not even need insurance, and he certainly doesn’t care enough to report it unless I do, so he leaves it up to me.
He helps me rope-up the bumper so it doesn’t fall off (he was such a nice guy, no joke!), and I start driving the rest of the way home. Quite a distance, mind you (I have a 60 mile total commute). After a little while, I start seeing smoke coming from under the hood. Fortunately it’s white, not black, so I know I’m not in terribly immediate danger. But I pull off to a gas station and take a look. Well, I can’t actually open the hood due to the scrunchy-ness, but I peer inside and see that there’s a significant bit of frame damage, and the radiator looks hurt. Sure enough, it would turn out that that was the biggest problem – the radiator (and compressor) would need to be replaced, and the frame around it needed repairing/re-welding.
This is not a small job. I take it to a body shop first, but as they look inside and see what I saw, they know that it’s beyond their scope, so they send me next-door to a full-service mechanic & repair shop. Next day, he gives me the estimate: $2.7k. Now, about this time, I’m talking with the insurance reps. I know they’re going to want to total this car – it’s KBB value is literally just over $2k, and these repairs are significantly more than that. What I was trying to ask them, and never got a straight answer, was whether we could file the claim for a lower amount, by asking for the mechanical repairs only. Remember, this car is a “beater”. We don’t really care how it looks, we just need it to run. And the shop was kind enough to provide that “bare-bones” estimate as well – only about $750.
But then my insurance adjuster did two things that were very insightful & much appreciated.
I have to give a shout-out to Safeco here, because throughout all of this, they’ve been immensely helpful and easy to deal with. (Even though I mentioned not answering my question in the paragraph above, as you’ll see, that was really my own fault for not understanding the process, and it was a moot point anyway!) So if you’re in the market for a new insurance policy, definitely check ‘em out.
First, because this shop was not an official “authorized partner”, she couldn’t accept their estimates as gospel; but, she could offer this newer “pilot” program whereby any shop (or even the customer) could submit pictures of the vehicle and the damage, and, provided enough detail and the right angles, a 3rd party estimator could assess the damage and estimate the cost. Great!
Second, she heard me out as I explained the concern with totaling the car, and understood that I really wanted to keep the car after simply getting it mechanically sound. But, she clarified, because I had collision coverage on this car, they (the insurance company) literally “owed me” the full cost of those repairs or the vehicle value, whichever is lower. So in fact, I would be doing myself a disservice and actually losing money if I tried to simply file the claim for the lower “bare-bones” amount, just to avoid the total-loss.
Instead, she explained, what you can do is keep the vehicle, even after it’s been declared “totaled“.
There’s a process and paperwork to this, and it involves the DMV, obviously. But because the insurance policy will still pay me the value of the vehicle, I should have more than enough to get the minimum repairs done and pocket the rest. Yay!
Now, the process. The CA DMV has done a fairly decent job of documenting this, but it’s still unclear (at least to me) what the order of operations is. There are 5 things you need:
Salvage title (which is different than the regular title, aka pinkslip)
DMV form REG 343, which you fill out yourself
REG 488c, which you also fill out yourself
Owner retention of salvage vehicle
REG 481, which your insurance company completes & sends to the DMV
Brake & light inspection (to make sure it meets road safety standards)
Certificates are printed & given to you by the inspecting shop
Full vehicle inspection (again, safety & compliance)
REG 31, which is completed by DMV personnel only
Number 4 can be done by many authorized 3rd-party shops, most of which also do smog tests and such things, so they’re not hard to find. The rest are DMV forms, as noted above. (#5 can be done either by the DMV or by CHP; but, CHP has quite a narrow list of “accepted” vehicles which they’ll inspect for this purpose, and honestly their appointment “system” for trying to get them done is horrendous, so it’s easiest to let the DMV do it.) But again, what’s the order in which I should do these things? Well, let me tell you!
First, you get that payout check from your insurance, and you get the repairs done. Then you take the car to a brake/light inspection place (#4 above), and get that “certificate” (much like a smog certificate, it’s an “official” record that says your vehicle passed this test). Actually, if the vehicle hasn’t been smog-checked recently, you probably need that too. Mine was just done in 2016 so it wasn’t necessary.
Ooh! Another database tie-in. Okay, we all know a car’s VIN is like the primary key of the DMV’s vehicle database, right? Plate#s you can change, but the VIN is etched in stone steel. But they’re largely sequential – so two 2000 Honda Accords are going to have mostly the same characters in their VINs, up to the last, say, 2-6 numbers (ish.. I’m nowhere near knowledgeable enough about the system, I’m just guessing based on my observation of what happened to me). So when the paperwork comes back from the insurance, it ends up with the wrong VIN, off by 3 #s at the end. But I don’t realize this until I check with the DMV as I’m filing the accident report. Also, you can use online services to look up a VIN and find the basic info about it, but again, because I had such similar VINs (my correct one, and the insurance’s one from the papers), both turned up the same descriptions, down to the body style and trim level (4 door sedan, LX, if you’re curious). The only way we actually found the mistake was that the DMV was looking up “ownership” info based on the VIN, and when the agent read me the name on file, I was like “whodat?”, since it wasn’t me or my father, and then I went back to my pinkslip and checked it there, as well as on the car’s door-panel.
The lesson here is, always double-check your VIN when filing paperwork, especially with the DMV. Moving on.
Before you go further, you need to actually make sure that the insurance and/or the salvage yard has officially notified the DMV of the vehicle being a “total loss”. (See #3 in the list.) In my case, they hadn’t – it had only been a month (between the actual payout and the first time I went to the DMV). So I have to check again before I go back.
But, since I was there, I made the DMV agent answer all my questions and specify exactly what I needed to do to complete this process, and the order in which to do it. Which is why I’m now writing this and sharing with you!
Once that notification is done, the DMV will have record of the vehicle being a “total loss”, or “salvage”. Then you can make a new DMV appointment, go in, and get #5 and #1-2 done all at the same time, in that order. I.e., go to the “inspection” or “inspector” side first, have them do the inspection and fill out the form (REG 31). Then go to the appointment line and take all your paperwork to the agent that calls you. So that’s your “inspection-passed” form (REG 31), your salvage title form (REG 343), your salvage certificate form (REG 488c), and your brake & light certificates. If you have a copy of the insurance co’s REG 481, might as well bring that too! You also need your license plates – you have to “surrender” them, which means turn them in and get new ones (not that same day, obviously – I think they still mail them to the DMV and you have to go pick them up… but I’ll find out soon).
Finally, to add a little icing on this crap-cake. I was driving the Hyundai to work, literally the next day, and I got rear-ended by another driver who wasn’t paying attention at a red-light. Again, super low speed, minor damage, but, another visit to the body shop for that poor black Elantra, and another week with a rental car. (Hyundai Santa Fe this time, which is actually quite nice, and if we need a small/mid SUV in the future, I’d definitely consider it; but due to my commute, we swapped for another Ford Fusion, this time the hybrid model, which again, I enjoyed, but the wife did not. Hey, you win some, you lose some.)
So that’s the story of how we totaled two cars (and damaged one car twice) in less than 4 months.
And that’s the reason I’m now taking a van-pool at least 2 days a week.
I’d always been a fairly safe & cautious driver, but I’ll admit, this long commute had turned me into a bit of a road-rager. Impatient would be the polite term. After all this, I’m back to my old cautious slow & steady ways… for the most part. I still get little flashes of panic when I go by the intersection where the Mazda wreck happened, and I’m always reminding the wife to stay cool and drink her water. She’s never had that happen before, and never felt like it since, so I’m sure it was a one-time fluke, but still.. the DMV wants her to re-test to get her license back, even after her doctors cleared her to drive. That’s a whole other topic, for another time. I will note that none of these incidents were due to cell-phone use, so at least we’re not guilty of that particular vice.