May 082015

It’s 10PM*. Do you know who your sysadmin role members are?

SQL Server’s fixed server roles control incredibly broad permissions across your entire instance. For this reason, membership in these roles should be granted carefully and reviewed often. Fortunately, reviewing role membership is extremely easy, so you can always answer the above question better than Homer.

The Easy Way: SSMS GUI

In the SSMS Object Explorer, open the “Security” folder for an instance, and then open “Server Roles”.

You’ll see it displays the 9 fixed server roles. Double-clicking on any role brings up a dialog that shows all its members:

If you hate typing, then this method is for you. Otherwise, you’ll probably like

The Easier, Faster Way: T-SQL

In SQL Server, where there’s a will, there’s a way to do something in T-SQL. And it will probably be better/faster/cheaper too. Here’s the query I use to check server role memberships:

SELECT AS Server_Role, AS Role_Member,
   mp.type_desc AS Member_Type,
   mp.is_disabled AS Login_Is_Disabled
FROM sys.server_role_members srm
INNER JOIN sys.server_principals rp ON srm.role_principal_id = rp.principal_id
INNER JOIN sys.server_principals mp ON srm.member_principal_id = mp.principal_id;

On my test server here at home, it returns the following results:

You can see the 4 columns it gives us: the name of the server-level role, the name of the login, what type of login it is, and whether or not the login is disabled. The SSMS role member dialog shown above gives no indication if a login is disabled or not, so I find that column especially handy in the query.

It’s important to note that this query returns members of all server-level roles at once. This example shows only the members of the sysadmin role because it’s the only one with members on this instance.

What to look for

  • First and foremost, do you recognize all the members of all roles, and should they have that level of permissions?
  • You’ll most likely see windows groups included in this list as well. Do all members of the group belong there?
  • You cannot remove the sa login from the sysadmin role, so I recommend either disabling it or changing its name.

Securityadmin: the most dangerous role

Ask anyone familiar with SQL Server what the most dangerous role is, and they’ll probably say “sysadmin.” While sysadmin has the keys to the kingdom in the sense that its members can do absolutely anything on the server, there is something securityadmin has that sysadmin doesn’t: secrecy.

Members of the securityadmin role can manage any login on the server, including membership in server level roles. This means someone with securityadmin rights can:

  • add themselves to the sysadmin role
  • do whatever dastardly deeds they want
  • and then remove themselves from from sysadmin

If you’re only looking at members of the sysadmin role, you will be none the wiser. When checking server-level roles, remember that securityadmin is every bit as powerful and dangerous as sysadmin. It just might save your bacon!

*It’s always 10PM somewhere

Feb 262015

Whether you are working in T-SQL, Oracle, MySQL, C#, or Java, the range of possible values for a signed (positive or negative) 32-bit integer is from -2^{31} or (-2,147,483,648) to (2^{31})-1 or (2,147,483,647). The fact that it’s so consistent across so many different platforms (and also against plenty of others I didn’t list) means there has to be more to it than just the preference of some developers somewhere, right? Exactly right.

Down to the bits

Any data your computer deals with, be it numbers, text, music or videos, all end up in binary at one point or another. Binary means values are in “base 2″, where each digit represents a power of 2, and the possible values for that digit are 0 or 1. A digit capable of storing only the values 0 and 1 is typically referred to as a bit, which is short for “binary digit”. This is in stark contrast to the decimal or “base 10″ number system commonly used, where each digit represents a power of 10 and the possible values for that digit are 0 to 9. To show how values are calculated in different number systems, here is the value 37 in both binary and decimal:

(click to enlarge)

I realize that’s an extremely brief example, but if you want to learn more about binary numbers, there are tons of great resources online.

But what about negative values? Negative numbers can be represented a bunch of different ways. Sometimes it’s done with the minus sign


or with parentheses


but those are all formats, or different ways of expressing a value. A format can be changed without altering the value itself.

How do you really represent that a value is negative? Since binary consists only of bits storing the values 0 and 1, what if the first bit of a binary number served not as a value, but to show whether it was positive or negative? A “1” in the left-most bit would effectively mean the value is negative, and a “0” would mean it is positive. Losing a bit to storing positive or negative would effectively cut the number of values you could store in half (since you are losing a power of two), but it would be awfully handy. There’s a little bit more to this method than that, but it’s known as…

Two’s Complement

Two’s Complement is a great way to express signed numbers in binary, for reasons we will see shortly.

Expressing a negative value via two’s complement is a rather simple process. Start with the binary expression of the positive value you want to negate. Next, flip all the bits so all the 0’s become 1’s, and all the 1’s become 0’s. If we were to stop here, this would be the one’s complement of the value. To get the two’s complement, we add 1 to the one’s complement. Here’s an example for the number 19:

010011  (19, unsigned, with leading 0 added)
101100  (bits flipped)
101101  (add 1. This is -19 in two's complement)

We now have the two’s complement of 19, which has the value -19.

Why did we have to add one? Let’s say we’re in a five-bit environment and want to find the two’s complement of zero (which would be negative zero). The number zero is represented as 00000 in this case. If we flip each bit, we now have 11111, and by adding one to that we now need a sixth bit so we can arrive at 100000. Since we’re still working in a five-bit environment, we ignore the leftmost bit, bringing us back to 00000, which represents both zero and negative zero. Having a single value for positive and negative zero is a key advantage that two’s complement has over ones’ complement.

An even larger advantage of two’s complement is that addition, subtraction, and multiplication work exactly the same as if all values were positive. You can even use the carry method to do this (like you were probably taught in school, but your kids most likely won’t learn thanks to common core in the US.) As a demonstration, let’s add 24 to the -19 value we just computed. The result should be 5.

(click to enlarge)

And indeed it is! If you’re paying close attention you will notice that I truncated the result to 6 bits and ignored the left-most carry. Restricting the result to the same number of significant bits you are adding is a requirement to arrive at a correct answer.

This is really neat, but it still doesn’t answer our original question about the possible range of values. Let’s look at the values you can create with 3 bits.

Three bits allow 8 or 2^3 values to be stored, and in the case of unsigned numbers, those values are from 0 to 7.  The two’s complement values of those same bit arrangements gives a range of -4 to 3.

Thinking of it in powers of 2, three bits in two’s complement allows us to store values from -(2^2) to (2^2)-1. Both the exponents are 2 instead of 3 because a bit is being used to determine whether the value is positive or negative. If we showed 2 as one less than three, the value range would look like:

-(2^{(3-1)}) to 2^{(3-1)}-1

So for n bits, two’s complement lets you express values ranging from -(2^{(n-1)}) to 2^{(n-1)}-1. This is exactly why for a 32-bit integer, the range is -(2^{31}) to 2^{31}-1.

And there you have it. Two’s complement is the reason why basically any signed data types have the range that they do.


Feb 242015

I’m definitely a fan of Wisconsin. I never really developed a taste for beer, but I do love Culver’s and cheese curds, so that counts, right? I’m also very happy to be presenting at SQL Saturday #387 in Madison on April 11 2015!

I had a blast at SQL Saturday Madison in 2012 and 2013. Last year I missed it, but with good reason – I was speaking at SQL Saturday Boston instead. I’m very happy to be heading back, though, and am looking forward to seeing friends and sitting in on some sessions by the other speakers in their amazing lineup.

My presentation this time around talks about all the wonderful new features in SQL Server 2014 other than In-Memory OLTP, more commonly known as “Hekaton”, which was it’s feature codename. Whenever SQL Server 2014 is brought up, Hekaton is usually one of the first things that comes to mind. It’s even got it’s own Wikipedia Page. It’s a neat feature, but SQL Server 2014 offers plenty of other awesomeness that far more people will benefit from. This session gives those other features some much-deserved love, and shows attendees how they can take advantage of all the new improvements.

If you haven’t already signed up for SQL Saturday Madison, do it soon – spots are filling up fast! Registration is still open as of right now. I hope to see you there!

Feb 182015

As of today the PASS Summit 2015 Call for Speakers is officially open! You can submit your sessions from right now until 9:00 PM Pacific Time on March 15, 2015. If you’ve had even the slightest desire to speak at PASS Summit, you should be submitting an abstract this year. Here’s why:

Because right now, you can

First and foremost, you only get the opportunity to submit sessions once per year, for about a month. Yes, it’s eight months ahead of time, but that’s a good thing. You’ll want to take advantage of all that time and perfect your slide decks, demos, and presentation techniques. If you don’t submit, you’ll spend that time wishing that you had, and telling yourself “I’ll do it next year…” and then we’ll have this same conversation a year from now. Wayne Gretzky said “You miss 100% of the shots you don’t take.” This is a shot. Take it.

What’s the worst that can happen?

Let’s hop in our time machines and travel back to my high school years. Instead of talking about submitting conference sessions, we’re going to talk about asking dates to a dance. I was single for 99% of high school and hence didn’t have a girlfriend as an “automatic” date. Instead I was always asking someone if they would like to join me for the evening, and of course it was stressful. Thankfully, my best friend was always there to reassure me by asking “What’s the worst that can happen?”

In the case of a high school dance, the worst that can happen is that the prospective date says no and never speaks to you again. If they were that insulted by such an offer and responded in that way, they obviously weren’t worth asking in the first place. This of course never happened. The far more realistic worst-case scenario is that they say no, and I still go to the dance anyway and have a great time with my friends who may or may not have dates.

Zipping back to the present, the worst that can happen here is that your submission is not accepted, and you still go to PASS Summit anyway and have a great time with all your friends who may or may not be presenting.

People want to hear your take on a topic

Really, they do. A common belief is that PASS Summit is the place to go to hear brain-melting half-day sessions on incredibly complex topics (yes I am thinking of you, Bob Ward). And of course you can find those sessions there, but for every presentation like that, there are easily 10 others talking about far less fancy (but equally important) things. Topics like backups and recovery, indexing, and query tuning may not be sexy and those features certainly don’t have cool code names, but they can make a difference to far more people.

We all work in different environments and use SQL Server differently. Your presentation on backups along with your stories and anecdotes will be completely different from mine, and that’s a wonderful thing. Never think that you can’t submit a topic because “they talked about it last year” or “that’s someone else’s thing to talk about.” Nobody owns the exclusive right to speak about any feature of SQL Server. Attendees can learn from your experiences just as well as they can learn from anyone else’s. People want to hear your take on a topic. Don’t be afraid to make it your own when crafting your abstract.

Even if you aren’t selected, you will learn something

A rejected abstract isn’t a failure, it’s an opportunity to improve and submit a better one the next time around. I’d venture a guess that most people submitting abstracts to major conferences for the first time don’t get accepted. There’s no shame there; you will have some great company. Make this the year that you submit and don’t get accepted, so you’ll be able to improve and get selected next year!

Best of luck on all your submissions this year!

Feb 132015

PASS Summit 2015Last week it was announced that the PASS Summit 2015 call for speakers will be open from February 18 to March 15. For those who are hoping to present there in October, it’s time to start getting those submissions ready!

If the past few years are any indicator, this year will see more submissions than ever before, probably over 1,000. While this will translate to tremendous variety in terms of speakers and topics, it also means some very tough decisions will have to be made by the members of the PASS Summit Program Committee, which is charged with selecting which sessions make it into the schedule. (And if you’d like to be part of the program committee this year, applications are being accepted until February 18. Apply today!)

I’ve had the pleasure of serving on the program committee for 4 years and have performed a variety of tasks including abstract proofreading for the guide, speaker qualification reviews, and abstract reviews. Having to read through and rate several hundred abstracts in just a few weeks is no small undertaking. And while the overall quality of the submissions increases each year, I’ve also seen some of the same errors time and time again. While they won’t necessarily kill your chances of having an abstract accepted, they definitely won’t do you any favors either. Based on the few years I’ve been reading abstracts, here are my tips for making sure your submission is in tip-top shape.

Complete all fields

PASS Summit session submissions involve filling in forms with multiple fields such as summary, abstract, prerequisites, and 3 goals for the presentation. Please make sure all of these are complete. In past years, the web interface has enforced this and not let you save a submission with empty fields, but some people still try to get around it by doing things like putting a few spaces or periods in a field. If you can’t take your submission seriously enough to think it through and fully complete the form, why should those on the reviewing end take it seriously either? When so many others fully complete their form, your chances of being accepted with an incomplete submission start to nosedive.

Have 3 goals

The list of goals tends to be a popular place to slack off. I can’t speak for 2015 yet, but in the past PASS has always asked for 3 goals your presentation will achieve. I’ve seen lots of cases where the same goal is copied and pasted into all 3 fields, and many other cases where whitespace or random characters are used to avoid listing actual goals. If you can’t come up with three distinct goals for your 75 minute presentation, I would start to question the strength of your topic.


Once your submission is complete, proofread it. Again. And again. You can never proofread too much. Fresh eyes help, so have others look at it too – maybe your coworkers, friends, or spouse. Even if they have no clue what you’re talking about, they can help you with grammar, spelling, and punctuation.

PASS Summit attracts presenters from all over the world, and for many of them English is a second or third language. I have tremendous respect for those people, as I don’t think I could ever feel comfortable enough with another language to present in it. This is one of the reasons I’ve never been one to nitpick on proper usage of “who” vs. “whom” or the presence or lack of oxford commas. My command of the English language is far from perfect, and I don’t expect anyone else’s to be perfect either. However some mistakes are so glaring they simply can’t be ignored. I can recall a submission talking about “SLQ Server”. Slip-ups like that are exactly why proofreading is a necessity.

Explain why your topic is important

Topics you submit to PASS Summit are clearly important to you because you’ve invested considerable time into writing a presentation and submitting an abstract for it. Make sure this importance is not lost on your audience by telling them why your topic is awesome and how it will help them. “Attend my session and learn how XYZ will help you quickly relieve the pains caused by issues A and B.”

Explain abbreviations

Abbreviations can be very helpful, especially for title fields with short character limits, but you can’t assume everyone knows the abbreviation you’re using. On more than one occasion I’ve had someone come up to me at a SQL Saturday and ask what “SSMS” meant. Fortunately the fix for this is very simple. If you’re going to use an abbreviation somewhere in your submission, make sure you spell it out fully elsewhere. For example, if your title is “SSMS Tips and Tricks”, maybe start your abstract off with “Most SQL Server DBAs use SQL Server Management Studio (SSMS) on a daily basis…”

Choose appropriate session levels & prerequisites

This one’s always tough. As part of a session submission you must assign it a skill level from 100 to 500. The PASS Summit 2015 information isn’t available yet, but the 2014 Definitions page defines these as:

  • 100 (Novice) Assumes some knowledge of the technical concepts/features, but not necessarily coding skills; 1 year experience.
  • 200 (Intermediate) Assumes comfort with technical concepts and basic coding skills; 1-3 years’ experience.
  • 300 (Advanced) Assumes solid knowledge of technology and strong coding skills; 4-6 years’ experience.
  • 400 (Expert) Assumes advanced understanding of technology; 6+ years’ experience.
  • 500 (Advanced Expert) Assumes deep technical knowledge of the technology; 8+ years’ experience.

I can’t tell you what skill level to assign to your session, but I can tell you that whatever you pick, the abstract and prerequisites should support it. Don’t say that your 500-level session has no prerequisites and that you are going to walk attendees through the topic from the very beginning. Similarly, don’t say that a 100-level session is for people who have been using a feature for 2-3 years.

Avoid metaphors & sarcasm

I love humor, metaphors, idioms, and sarcasm, but their downfall is that they can lead to lots of confusion. While sarcasm can be fairly obvious when speaking to someone, it doesn’t usually work very well when it’s being read. Just think about all the times you see things like “</sarc>” or “totally being sarcastic” in tweets and posts online. I’m all for making PASS Summit as fun and light-hearted as it can be, but I think a little humor will get you much farther being included in your presentation than it will in the abstract.

Keep it positive

PASS Summit is an incredibly positive experience; don’t take away from that by adding negativity to your submission. There is absolutely no need for titles such as “5 Dumb Mistakes Rookies Make” or abstracts like “Attend my session and learn how to show those stupid developers who’s boss!” We’re all there to learn from each other, and nobody is stupid. Not only can negative wordings discourage people from attending, but they can also set the tone for the entire session.

I hope this helps, and good luck with all your submissions!