The SQL UPDATE Statement

This quick write up comes courtesy of a tweet by Jonathan Kehayias (@SQLSarg) yesterday morning (Please Note: OH means overheard, meaning that statement is something Jonathan overheard. He’s WAYYYYY too smart to actually spread something that dumb as valid advice) . Here’s the tweet:

Ok, so I’ve seen a couple of stupid things written up in the last few days but this one just might take the cake. If you have been a DBA for any amount of time then you’ve more than likely come across a vendor application that uses the ‘sa’ account for access to the database. I won’t get into details about the sa (or system administrator) account here but check out this article by Ken Johnson at SQLServerCentral.com about it (check out the discussion thread as well to learn more).

So what exactly is wrong with that statement in the tweet? Well, as stated by Jeff Smith (@hillbillyToad) this morning:

“Ok Jorge, stop making fun of me”. No, as long as you access things using sa for “simplicity” or “optimization” I’m going to beat this over your head like an Acme mallet. Using ‘sa’ account for everything is akin to being handed the keys to the bank and being told “yeah, go ahead and make your deposits and withdrawals from your own account but try not to touch anything else while you’re digging around the vault”. Seriously, I’m not kidding. Handing someone the ‘sa’ account is handing them the keys to your SQL kingdom. Think about it, if you write an application that is accessing your database with FULL admin rights, what if someone performs a SQL injection attack and drops your production tables for kicks?

SQL Injection: It happens

Listen folks, I know that security can be a pain but it’s there for a reason. Don’t get lazy and just assume the user needs an admin account to access the database because 9/10 times it doesn’t. You could probably get by fine on creating a new schema with write/read access and maybe EXECUTE stored procedures permissions. In fact, secure yourself from SQL injection attacks by wrapping your code in stored procedures in the first place.

There’s a ton of resources out there to learn how to properly secure SQL Server. Get up to speed by reading up about Security and Protection on MSDN. There’s also tons of videos and demonstrations out there. Check out the Quest Pain of the Week webcast on SQL Injection courtesy of Brian Kelley (@kbriankelley) and Kevin Kline (@kekline). Finally (WARNING: Blatent self-promotion inbound) make sure to check out SQL University’s security week from Semester 1. Bottom line is if someone tells you “this application needs to run as sa”, have them give you a detailed explanation as to why. Part of your job as a responsible DBA is to protect your data and your database servers. If they simply don’t know any better then offer to educate them on schemas, security groups, etc.  And remember, “because its an optimization” is a stupid answer.

HERE THERE BE DBAs

This is just a quick post to serve as a warning sign to those who are DBAs in shops where you routinely get vendor pre-packaged databases (which should encompass about 99% of you). Today we had a vendor installing a new application which required a SQL database, to which I am ever thankful. Unfortunately this database, as I came to discover after a discussion with my junior DBA, that it was actually a restore of a database that was a created after mashing a bunch of data together from disparate systems. Ouch.

Well long story short after the install I took a look at the database server’s (disclaimer: I put them on a shared SQL instance since the app is relatively small) error log to make sure nothing crazy was happening since during the install they had issues with account creations. FYI that turned out to be a small “we need sysadmin” rights issue to which I said ‘NAY!’ and granted them temp rights to complete their install and then revoked those rights when it was complete. Anyhow the error log showed me something interesting:

Message
SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.

Well that’s interesting. I quickly Banged it out and the first solution I found was a KB article (KB917828) that said these were “normal” messages you’d see in a database restored starting from SQL 2005 SP2 and upwards. I read on as I needed to know WHY these messages appeared and BAM, something hit me and sent off alarms:

The whole procedure cache is cleared when certain database level operations are performed in the following scenarios:

• A database has the AUTO_CLOSE database option set to ON. When no user connection references or uses the database, the background task tries to close and shut down the database automatically.

Auto_close enabled on a database on my server? NOT ON MY WATCH! I quickly jumped over the database options and sure enough not only was AUTO_CLOSE enabled but AUTO_SHRINK as well. For those of you curious as to why I’d freak out about this, these options are really resource intensive and this being a shared instance I really don’t need that hassle. If you’d like to read about how/why these two options are bad check out Tim Ford’s blog at http://www.ford-it.com (aka @SQLAgentman on Twitter) and read his post on Dealing with SQL Shrinkage.

So remember kids: check, re-check and double-check what your vendor is doing when they setup databases on your systems. Ideally they should provide scripts for you to be able to validate and evaluate before you just “slap it in” to your production environment.

So unless you’ve been living under a rock for the last few weeks/months, you may have heard about a little thing called Windows 7. Windows 7 is Microsoft’s latest iteration of its Operating System and along with a slew of other stuff (i.e. security enhancements, pretty new stuff to love, Windows management features, etc.) there’s a nice little feature I find comes in handy, especially when you work with a global community like PASS (and you SQL peeps thought this post wasn’t going to pertain to you…)

The feature I’m talking about is multiple clocks in your system tray. I’m not sure if this was available in Vista as well but I thought I’d share with everyone how to do it in Windows 7. First click on your clock in the bottom right-hand corner.

Next click on the ‘Change date and time settings’ link to bring up the Date and Time box. Click on the tab for Additional Clocks.

Once here you can add up to two additional clocks in addition to your default clock for a total number of 3 possible clocks! Simple check the ‘Show this clock’ box to enable the clock. Use the dropdown menu to select the appropriate time zone you wish to monitor. You can also give the clock a custom label for display purposes. Once you’re done click OK.

Now if you click on your clock in your taskbar again you should see your newly added clocks!

I live in Florida so the clocks I like to monitor are either West Coast or India since those are the two time zones I tend to end up working with the most. Sure I could probably do the math for someplace close like the West Coast but I’m a geek, and I like to see my information quick and at a glance.

And since we’re talking about Windows 7, I’ve included in this blog a zip file of step-by-step directions on how to do this (screenshots included) courtesy of another really cool feature in Windows 7 called the Problem Steps Recorder.  With this tool you can see step-by-step how a user got to a problem or you can use it to document an issue like I have here.

Download Zip File

Good afternoon students, this is just a quick announcement to let you know that SQL University (sort of) is offering a computer lab for students to cut their teeth on SQL 2008 technology. I say sort of because this lab is actually offered via a partnership with PASS, Maximum ASP, and DELL Computers at http://www.sqlserverbeta.com .

The labs offer up a virtual sandbox environment where you can discover and practice everything you learn here in a dedicated hosted environment! Here’s the lab description:

We will be adding additional labs in the coming months in preparation for the release of SQL Server 2008 R2, so check back often.

So how do you get in on this great deal? Well just head on over to http://www.sqlserverbeta.com and Register!

In Tuesday’s class we covered the different authentication methods as well as did a quick walk-through of the SQL Management Studio interface. In today’s lecture we’ll continue looking at SSMS. Some of the things we look at include how to create a query against a database, brief intro to the Profiler tool as well as a brief intro to the Database Engine Tuning Advisor.

Warning: Video is hosted by YouTube. If you cannot see it your company might be blocking that site. My apologies, I will have an alternative method available in future.

In addition to watching the video you guys have a little bit of homework. Don’t worry, it’s not hard. Just try a few of the things on this list to help you get accustomed to the environment.

• Connect to your local database using SSMS using Windows authentication
• Browse to the AdventureWorks Database, Open up the Tables folder and select the TOP 1000 Rows from Person.Address table. In comments below, post the AddressLine1 for AddressID 17.
• [Extra Credit] Browse to the Security folder, Open up the Logins folder. Create a new login called testlogin. Make it SQL Server authentication, give it password of ‘P@$$w0rd’ (NOTE: Thanks to Brian Kelley for pointing this out. Do NOT use your production servers/databases for this excercise. Also NEVER use password this simple in your production environment…ever. Security will be covered in a few weeks.), make its default database AdventureWorks.

Next week we’ll be covering Backup and Restore with Argenis Fernandez (Blog | Twitter). The full syllabus for the semester will be posted shortly.