A Wandering Mind

Twice over the last week I’ve run into a scenario where a user is trying to execute a stored procedure and gets a message similar to the following:

Msg 1088, Level 16, State 7, Procedure p_TruncateTest, Line 5
Cannot find the object "TestTable" because it does not exist or you do not have permissions.

At first this was a bit puzzling, because the user in question had both EXECUTE rights on the stored procedure, along with VIEW DEFINITION rights across the schema. I’m used to the idea that as long as a user is granted rights to execute a procedure, and the procedure is owned by the same principal as the underlying object being manipulated, no other permissions are checked. It’s one of the fundamental reasons why stored procedures are such a good practice.

In this case though, things were a little more complex. It turns out that the line in question was issuing a TRUNCATE statement. Now, if this were executed via dynamic SQL, I could understand this being an issue. But it was hardcoded; nothing dynamic about it. That being the case, I wanted to see if the behavior was reproducible.

To the lab!

Sure enough, the same message popped up. So it seems the TRUNCATE statement does not behave the same way as other DML style statements, such as SELECT,INSERT,UPDATE,DELETE. So that got me thinking: was this the same for other DDL statements?

After executing this I was greeted with the same exact error message.

Ok, so the behavior is consistent with other forms of DDL. That being the case, I needed to find some alternatives to actually granting the user the full ALTER rights on the objects in question, since that would allow them to do all sorts of other nasty things.

Enter EXECUTE AS.

From this BOL article, we see that we can use this nifty clause to effectively elevate a calling user’s rights for the duration of the stored procedure execution. In this case, we’re going to have the procedure execute as the ‘dbo’ user. Normally I’d say it’s a better practice to have the execution be under a less privileged user, such as one that owns the application schema, and not one in the db_owner role.

Voila! The procedure executes without issue.

Now there is one catch here: when using that WITH EXECUTE AS clause you have be careful about how you determine the name of the user performing the operation. If, for example, you use the CURRENT_USER function to log who issued the command, what you’ll see is the user named in the WITH EXECUTE AS clause, not the calling user. Instead, make sure you use the ORIGINAL_LOGIN function. Thanks to Kimberly Tripp for pointing this out.

One of my latest projects has been to get a VPN link up between my parent’s house and ours, so that I can help them out with computer issues without having to make the trek down to West Chester. Not that I don’t love visiting them (shamless parental plug, they do read this after all), but what with Taylor’s arrival and all we don’t get out much these days. Anyway, after thinking about how best to accomplish this I decided I should use a DD-WRT (open source alternative firmware) flashed Linksys router for the job, for a number of reasons:

• Thanks to the generousity of a local gentlemen I know from Twitter I acquired one of these devices for free. Don’t ever say Twitter can’t get you stuff!
• It’s low power and unobtrusive. While I love my big ol’ quad core monster, I didn’t really want to take up space at my parents house.
• With DD-WRT, it functions great as an OpenVPN client.

Now, the setup was to look something like this:

(Please excuse the rather awful drawing, I’m afraid I’m used to Visio)

Once I had everything setup, everything appeared to work, except I couldn’t ping anything at my parent’s end of the tunnel from my home network (vice versa worked fine). Well, normally I might try and sniff the traffic at the two endpoints, but this was a little more complicated than you might think, since the router at my parents doesn’t support such an operation.

In then end, I was able to collect some data after hacking a solution (<geekery>I used an already compiled version of tcpdump and manually moved it to the router</geekry>), and figured out that for some reason the VPN router at my paren’t house was performing what’s known as “masquerading”, or “NATing”. What this means is that any traffic that passed out of it was translated such as to appear that it came directly from the router itself, and not some machine behind it. This novel concept is actually the basis for how pretty much every home router functions; but in this case it was bad, in that it made the tunnel between our networks essentially one way.

So what did I do? Well get royally annoyed of course, proceed to spend the next several hours pouring over the setup of both ends, trying various hacks, googling like crazy, and perhaps questioning my intellect (or that of the programmers of OpenVPN, DD-WRT, and Linux in general) a few times. Finally after giving up for a day, I decided I’d look at a tutorial on the OpenVPN site itself I remembered. It took awhile to find, but there was the answer, right in front of me.

Apparently OpenVPN needs a little special tweaking to allow for traffic from both the “client” (my parent’s house) and “server” (my house) networks to fully talk to each other:

<geekery>

Next, we will deal with the necessary configuration changes on the server side. If the server configuration file does not currently reference a client configuration directory, add one now:

<strong>client-config-dir ccd</strong>

1	<strong>client-config-dir ccd</strong>

In the above directive, ccd should be the name of a directory which has been pre-created in the default directory where the OpenVPN server daemon runs. On Linux this tends to be /etc/openvpn and on Windows it is usually \Program Files\OpenVPN\config. When a new client connects to the OpenVPN server, the daemon will check this directory for a file which matches the common name of the connecting client. If a matching file is found, it will be read and processed for additional configuration file directives to be applied to the named client.

The next step is to create a file called client2 in the ccd directory. This file should contain the line:

<strong>iroute 192.168.4.0 255.255.255.0</strong>

1	<strong>iroute 192.168.4.0 255.255.255.0</strong>

This will tell the OpenVPN server that the 192.168.4.0/24 subnet should be routed to client2.

Next, add the following line to the main server config file (not the ccd/client2 file):

<strong>route 192.168.4.0 255.255.255.0</strong>

1	<strong>route 192.168.4.0 255.255.255.0</strong>

Why the redundant route and iroute statements, you might ask? The reason is that route controls the routing from the kernel to the OpenVPN server (via the TUN interface) while iroute controls the routing from the OpenVPN server to the remote clients. Both are necessary.

Yep, you read correctly. “Both are necessary.” Long story short, I only had one of the two necessary configurations in place. Basically, while the normal

command got the packets as far as the OpenVPN tunnel, the software required that extra

configuration line to correctly send the traffic on its merry way from there. Without it, the traffic would just appear to go out the proper interface, but actually go to that awful place where packets disappear and are never seen again.

</geekery>

Hi everyone, my name is Josh, and yes, I admit it, I should have read the… oh hell you know what the rest means.

When I first started up this blog, I had a fairly clear vision of where I wanted to go with it. Having browsed the musings of established GTD bloggers like Merlin Mann of 43folders.com and Andrew Mason of Did I Get Things Done, I wanted to follow in their mold of becoming an established voice in the field. But then along the way, it seemed that I hit a wall. You see, one of my goals has always been to have some useful, original content to post; trouble is, GTD is such a hot topic these days that original thought or advice is rather hard to come by.

Now granted I could talk about how to set up your “trusted system”, or how to maintain discipline with starting tasks, etc. But the truth is, someone has probably already written about it, and with far better clarity and experience than I could claim to have. I’ve been at this game for just under two years now; hardly enough time to call myself a guru of any kind. So what’s a disenchanted productivity geek to do?

In short, I’ve decided to take the blog in a bit of a new direction. Instead of trying to write some authoritative pieces on how you should integrate the concepts of GTD into your life, I’m going to keep it rather simple, and just talk about me. Now before you ask, this is not going to turn into some ego-maniacal diatribe on why Josh Is God or some such nonsense. No, instead, I’m just going to write as what I am: a guy coming from a world of disorganization and ADD-exacerbated messiness, trying his damndest to learn how to keep everything together (or at least keep appearances of such, but don’t tell my boss).

From now on, it’s going to be a more journalistic approach. I.e. “I read this really interesting post the other day, here’s how I’ve tried to incorporate it, here’s why it worked / didn’t work.” I’ll be happy to tell you all about my struggles and triumphs, and maybe even those of others I meet or talk to. But from now one, the one thing I’ll try not to do is tell you how to do things. If you ask (comments still encouraged), I’ll be happy to give an opinion, but outside of that, the advice column is closed.

Beyond that, I’m also going to try and expand a bit on the non-GTD topics that are holding my interest. I think a while ago I wrote a pretty decent introduction to a series on e-mail encryption… maybe it’s time to pick that up again, eh?

Here’s to a little diversity and change in focus. Hopefully it will be just the thing to help break out of a bit of writer’s block and get this blog up and running again.

While I am not a fan of Microsoft Auto Update due to a little piece of, ahem, semi-spyware it installs, for those who are less technically inclined it still gives you an easy and safe way to ensure your computer is kept up to date. This is extremely important as Microsoft releases updates monthly (more frequently these days) which help prevent hackers and other malcontents from taking advantage of your computer’s generosity.

Degree of Difficulty: 1 (Grandma)

Note: All tips published will have a difficulty rating of 1 (Grandma could do it), 2 (Normal User), 3 (Power User), and 4 (Geeks only).

To access the screen shown above, click on Start, then select Control Panel, and Automatic Updates. Then, select the button titled “Automatic”. You can then choose when you want Windows to download and update your system. Hint: pick a time in the early hours of the morning, when you can leave your computer running overnight. Wednesday at 3:00AM is a good time, as Microsoft routinely releases patches on the second Tuesday of the month.

Most of the time, the update process should be completely transparent to you, and your computer should be ready for you to check your e-mail with the morning coffee. Occasionally however, you may run into an update that requires you to accept a user agreement, or requires a restart of your PC. If this is the case, you’ll see some kind of on-screen notification, usually in the form of one of those little bubbles in the bottom right corner.