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.

Continue reading

Eliminating Static Output Logs

Using Tokens to Create Dynamically Named Job Step Output Logs

There are situations that make me think I need to find and enroll in a “Making Better Google Searches” class.  After some brief digging into Ola Halengren’s Maintenance Solution I inadvertently came across the way of creating log file names dynamically for job step output.  While this may not sound all that wonderful, I’ve asked a lot of people how to do this over the years (including MS support during various incident resolutions) and no one has been able to tell me what it took 2 minutes of digging into Ola’s solution to find.  I think the problem isn’t the folks I asked, but how I asked the question.  Anyway, read on and I will show you the way to create dynamic output log names within a Job Step’s Advanced Tab.

Continue reading

Bucking a “Best Practice”

Why avoiding the “Change port 1433 Best Practice” might be the “Better Practice” to follow

A lot of quality resources in the DBA community (e.g. MS, Jonathan Kehayias, Green SQL, etc.) state that changing a default instance’s port to something other than 1433 is a security “Best Practice.”  While I may agree that you want to avoid using port 1433, I contend there are better ways to go about it.
Continue reading

Hooray! You finished installing SQL Server, now what?

John’s post-install configuration recommendations

Let me start by saying this is the procedure I perform when configuring a new SQL Server instance, immediately after installation.  “Best Practice” is a term a lot of people love to throw around, but I will not definitively say performing the following is a “Best Practice” as your environment and situation could be wildly different from mine.  I find though, that these steps work best for me in most situations.  With that said a lot of the steps outlined below do have “Best Practice” associated with them from one source or another, but they could range anywhere from highly useful to absolutely worthless for your environment so keep that in mind as you read on.
Continue reading