What is Your Biggest Weakness?

I was tagged by Ken Simmons (@kensimmons) in the blog chain started by David Stein (@made2mentor).  Here’s Ken’s post and the original.  There are several other posts including ones by Brent Ozar (@BrentO) and Thomas LaRock (@SQLRockstar).  So I guess I’ll add mine.

This is a very hard post for me to write for two reasons:

1. Who likes to publicize where they struggle?  I know I prefer to talk about successes and strengths.
2. I tend to be very critical of myself (and others many times), See Joe Healy’s comments on my blog post about areas where SQLSaturday #21 – Orlando could have been better, and others usually have higher regard for my knowledge/abilities than I do, so it’s hard for me to pick one weakness as my biggest.

Maybe procrastination is it, as I know you are all saying, “Get to the point, what’s your biggest weakness?”.  Okay, okay, I’ll pick one.

I’d say my biggest weakness is lack of confidence.  I know I may not come across that way, but while I may know something intellectually I hesitate to implement for fear I may be wrong.  In some ways this is a good thing because it makes me plan, learn, and test more, but it can be a bad thing because it can make me get stuck in one place when I should be moving to another.  The funny part of it is, each time I finally move or when I do the thing that I’m afraid of, none of the things I worried about happened.  For example, whenever I speak, whether on technical topic or in elsewhere (I speak in church regularly), I’m EXTREMELY nervous and think “What if someone in the crowd knows more than me and shows where I’m wrong?” or “What if the projector or demos don’t work?”, but once the talk starts, I’m fine.  I’ve spoken probably hundreds of times, and I still fight the fear.  I do the same thing when I learn something new for SQL Server or when it’s time to apply a Service Pack or upgrade, “What if it doesn’t work?”.  Well, experience tells me it most likely will work, especially if I tested it, and experience also tells me that I’ve recovered from disasters before, so I can probably fix it.  It’s funny though because the only time I’ve had problems is when I didn’t worry at all, what does that mean?

I’m not sure all who have been tagged, so I’m not going to tag anyone else.

Do You Use Projects/Solutions in SSMS?

When SQL Server 2005 was released Microsoft made a major change to the client tools provided with SQL Server, moving from Enterprise Manager for management tasks and Query Analyzer for scripting to the Visual Studio-based SQL Server Management Studio.  I have to admit that when SSMS was released I didn’t like it and continued using Query Analyzer for a while.  I have now adjusted to SSMS and, while I still wish I had a simple query tool, have grown to appreciate what it does.  My two favorite features are the Standard Reports and the ability to add Projects and Solutions like in Visual Studio.
I have to admit that I don’t use the Project and Solutions feature enough in my day to day work, but I do use it when I put together a blog post or presentation for a SQLSaturday or User Group.  By putting all the code for the demos in a Project I don’t have to go searching the file system to find a demo, I don’t have to have the code pre-loaded into SSMS, and I can stay in SSMS throughout the demos.  Here’s my project for my Default Trace presentation:
As you can see it provides a single place for all your scripts.  You can also add the Project/Solution to your source control product of choice (I don’t have one installed on my personal laptop at this time). 
I will be working with this feature more to have projects for my database scripts, maintenance scripts, and my favorite queries in addition to using it for presentations and blogs.
I’d love to hear how you are using projects and solutions in SSMS.

OPASS December Meeting Recap

This was the first meeting where I really lead most of the meeting.  Andy Warren (@sqlandy) did the networking at the beginning and the raffle at the end while I did announcements and set the agenda.  As usual, I forgot to mention something at the beginning, the Florida Microsoft Speaker Idol competition for which we had two takers (Tommy Bolhoffer, probably misspelled, and Robert Hurwitz, again probably misspelled).  Exciting times at OPASS!

We started the meeting with some time to meet and talk to someone you didn’t know or didn’t know well.  I spoke with Ronnie, Lisa, Rob, and several others during this 10-15 minute time.  Then I went over the PASS November-December slide deck and announcements, and Andy, Kendal Van Dyke (@SQLDBA), and I shared some of our takeaways from the PASS Summit to show the value in attending and encourage others to attend.

Our main speaker was Gus Gwynne (GSquared on SQLServerCentral) speaking on “Auditing and Logging”.  He did a very good job.  He shared reasons for auditing (Blamethrower was my favorite, though not his), different types of auditing which include passive (log reading, tracing) and active (triggers, logging within the transaction).  He also shared several examples using triggers and had some good stories to illustrate how logging has helped in his workplace.  One of the interesting stories was when an outsourcing company updated the last name and password for all users in a database, and, because a trigger logged the changes, he was able to quickly (within minutes) undo the update.

We then raffled off a few prizes, a Microsoft Arcmouse, Office 2007 Standard, and Windows 7 Ultimate 64-bit were the big ticket items.  After the raffle we had some good discussion about speaking, self-promotion, and personality.

All in all a good night.  Now I need to find a speaker for January.  If you are interested contact me, @unclebiguns (blog post here for why that’s my handle) on Twitter or via the contact me link on the blog.  We are open to do a Live Meeting or having you come in person.

T-SQL Tuesday #001 – Dates and Times

T-SQL Tuesday was started by Adam Machanic (@AdamMachanic)  on his blog to encourage SQL Bloggers to share their tips and tricks about a specific topic once a month.  A great idea and a great way to get a topic to blog about!
I’ve been working with SQL Server since 1999 and it wasn’t until the last couple of years that I finally learned a better way to retrieve a date range.  I used to do:

SELECT
columns
FROM
TABLE
WHERE
date_column BETWEEN start_date AND end_date;

I’m guessing that there are people reading this that are saying, “I do that all the time and it works fine for me, so what’s the problem?”.  I understand where they are coming from, but I finally really understood that, until the specific DATE data type (date only) in SQL Server 2008, datetime and smalldatetime columns ALWAYS have a time part.  This can and does affect date range queries.  Here’s an example, albeit slightly contrived, but I’ve seen it happen in the real word:

DECLARE @sales TABLE (sale_id INT IDENTITY(1,1) PRIMARY KEY, sale_date DATETIME, sale_amt FLOAT);

WITH cteNums AS 
(
SELECT TOP 50
ROW_NUMBER() OVER (ORDER BY AC.NAME) AS N
FROM
sys.all_columns AS AC 
)
INSERT INTO @sales (
sale_date,
sale_amt
)
SELECT
DATEADD(DAY, -N, GETDATE()) AS sales_date,
N * ABS(CHECKSUM(NEWID()))/10000.00 AS sale_amt 
FROM
cteNums 

/* 
Setup for contrived example.
Make sure the first sale of the month is at midnight 
*/     
UPDATE @sales
SET sale_date = DATEADD(DAY, DATEDIFF(DAY, 0, sale_date), 0)
WHERE
DATEPART(DAY, sale_date) = 1

/*
Task is to get sales for the previous full month
*/
/*
Set up variables for the first and last day of th month
*/
DECLARE @start_date DATETIME,
@end_date DATETIME

/*
Set the variables to the first of last month and the last day of 
last month at the time of writing '2009-11-01' and '2009-11-30' See this blog post by Lynn Pettis for why I am using DATEADD and DATEPART with 0
*/        
SELECT
@start_date = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0),
@end_date = DATEADD(DAY, -1, DATEADD(MONTH, 1, @start_date))

SELECT @start_date, @end_date, DATEADD(DAY, 1, @end_date)

/*
My old method
*/
SELECT
COUNT(*) AS sales,
MIN(sale_date) AS first_sale,
MAX(sale_date) AS last_sale,
SUM(sale_amt) AS total_sales 
FROM
@sales
WHERE
sale_date BETWEEN @start_date AND @end_date 

/*
My new method - accurate
*/    
SELECT
COUNT(*) AS sales,
MIN(sale_date) AS first_sale,
MAX(sale_date) AS last_sale,
SUM(sale_amt) AS total_sales 
FROM
@sales
WHERE
sale_date >= @start_date AND 
/*First of the next month */
sale_date < DATEADD(DAY, 1, @end_date)

Now I'm sure someone out there will say, “Hey in your last example you are using the first day of the next month and you could do that with between.” Well yes I could and here is the query:

SELECT
COUNT(*) AS sales,
MIN(sale_date) AS first_sale,
MAX(sale_date) AS last_sale,
SUM(sale_amt) AS total_sales 
FROM
@sales
WHERE
sale_date BETWEEN @start_date AND DATEADD(DAY, 1, @end_date)

But this does not return the correct results either.  Here are the results for each query:

	Count	First Sale	Last Sale	Total
1st Between	29	2009-11-01 00:00:00.000	2009-11-29 11:09:30.107	86362080.49
>= and <	30	2009-11-01 00:00:00.000	2009-11-30 11:09:30.107	86362080.49
2nd Between	31	2009-11-01 00:00:00.000	2009-12-01 00:00:00.000	86362080.49

All that to show that I believe you are better off using >= and < instead of BETWEEN when comparing dates.

Good Programmer = Lazy & Dumb?

Alternative title for programmers using C-derivatives and Java:

Good Programmer == Lazy & Dumb?

I read this blog post, Why Good Programmers Are Lazy and Dumb, and it struck me a being an excellent perspective.  Especially with these 2 quotes:

Lazy, because only lazy programmers will want to write the kind of tools that might replace them in the end.

My goal when working on a project it produce a product that takes me out of the loop on the project.  By that I mean that I want to provide the end users all the tools they need to manage the application once released to them.  No, I don’t want to have to manage in-application security, I want a tool provided to the appropriate person to do that.  I want to be a DBA\Developer, NOT an application administrator.  I want to be able to work on new and exciting projects, not stick with one.

But there’s a more crucial point why a good programmer must be dumb. That’s because for him to find the best solutions to problems, he must keep a fresh mindset and manage to think out of the box (or rather, know its actual shape).

Sometimes, the worst situation is the one where you are re-writing an existing application.  Why?  Because you, or someone else on the team, knows too much about it and can’t see new ways to do it.  A few years ago I moved to a new position and was put on a project with one of the existing developers who had been in the organization for several years and had written much of the current application.  Let’s just say this was a blessing and a curse.  They knew the processes well and what hadn’t worked well, but this also meant that they weren’t able to see other ways to do things which may be better.  I was the “dumb” programmer in this situation and tended to ask, “Why?” a lot and suggest alternate ways of doing things.

So the questions are:

1. Are you trying to write software that replaces you?  Are you automating the routine tasks of being a DBA so that, with proper documentation, someone can come behind you and not miss a beat?

   I know I have a long way to go in regards to automation and documentation, but I’m working on it.

2. Are you content with, “I’ve always done it that way”, or are you looking for new or better ways to do things?

   I’d like to think that this an area I do well in.  I’m always looking for a better way to do something, which is why I attend user group meetings, read books and blogs, and participate in the online SQL Community.

How about you?

Book Review: Apress Pro SQL Server 2008 Administration

Several months ago I received a copy of the Apress book, Pro SQL Server 2008 Administration (Amazon) by Ken Simmons (@kensimmons), a friend of mine, and Sylvester Carstarphen (this is my disclaimer that I didn’t buy the book).  Well, I finally finished the book and can post my thoughts.

General Impression

This was a good book, but my first thought was that I expected more from a “Pro” level book.  I’m not sure if this is because my expectations were too high, or because I underestimated my own knowledge.  What the book does cover it covers well and the writing is excellent, but I didn’t think that a “Pro” level book would include doing an install or upgrade from the GUI, which this book includes and I thought that there were a lot of pages on what I would consider basic level tasks (security, indexing, backup & restore).  

The Book

I liked how the book starts with a three chapter section, Introducing Microsoft SQL Server 2008, covering new features, pre-installation, and high-availability options. 

Part 2 – Getting Started

Three chapters on Installing, Upgrading, and Post-Installation. The end of chapter 5, Upgrading to Microsoft SQL Server 2008, where the authors covered post-upgrade tasks like changing compatibility levels, updating statistics, page-verify settings, etc.. was excellent as these are often forgotten or overlooked tasks. 

Part 3 – Administering Microsoft SQL Server 2008

The meat of the book with seven chapters on Multi-server Administration, Managing Security, Administering Database Level Objects, Indexing, Backups, Restore Strategies, and Automating Routine Maintenance. 
Chapter 7 which covers Multi-server Administration, including Policy Based Management and Central Management Servers was excellent, although I think more time could have been spent on these features, especially if less time was spent on the GUI install and upgrade. 
Chapters 8-11 did a good job explaining the mechanics and the how-to’s of security, managing objects, indexing, and backups, but I assumed this would have been covered in a beginner or intermediate level book and a pro book would have spent more on the strategies and why’s of these topics. 
Chapter 12 on Restore and Recovery Strategies does spend more time on the reason for the different restore options and did a better job hitting higher-level topics like Page restores, piecemeal restores, and online-restore options which is why this is one of my favorite chapters. 
Chapter 13 – Automating Routine Maintenance, was one I was a bit disappointed in.  It does a good job of covering Database Mail, SQL Server Agent, and Maintenance plans, but I thought that there would be more on writing and using scripts like Ola Hallengren’s Maintenance scripts or Michelle Ufford’s (@sqlfool) index maintenance script to show how the author’s or others automate and plan for maintenance.

Part 4 – Troubleshooting and Tuning

Three chapters on monitoring, auditing, and managing query performance.
Chapter 14 covers monitoring and does a good job explaining the tools available (Perfmon, DMV’s, DBCC, Profiler/Trace), including the counters and events to watch.  I especially liked how the authors showed how to get performance counter information both from PerfMon and the DMV’s.  Baselining is discussed but one thing is missing, and I haven’t found it anywhere yet, is how/where to store your baseline data for comparison later.  I know I could figure something out, but I want that one to be given to me.
Chapter 15 covers auditing.  Actually it covers the new enterprise edition only SQL Server Audit.  It’s a good chapter and covers it well, I guess, but since I don’t run enterprise edition, I didn’t really play with it all.  I still wish the book had covered some other ways like SQL Trace, the Default Trace, and Common Criteria auditing which are still available even at the non-enterprise level.
Chapter 16 was on managing query performance and included hints on using Profiler and PerfMon together, using the standard performance reports in SSMS, DMV’s, query hints, plan guides, and Resource Governor.  All in all a very informative chapter on performance monitoring/tuning for an administration book.  The chapter does mention another Apress book, SQL Server 2008 Query Performance Tuning Distilled (Amazon), as a more detailed resource for this information (I have it and it is, a review will be coming).

Part 5 - Conclusion

Two chapters covering Secrets to Excelling as a Professional DBA and What’s Next which were excellent as they talk about knowing your limitations, communicating, being a leader, and taking charge of your own professional development.  These are all areas where I can see myself needing growth.

My Conclusion

I recommend the book as I think people at all but the extremely low or extremely high levels will get something from it, but people moving from SQL Server 2000 to 2008 and junior DBA’s will get a lot from it.

Maintaining Security and Performance Using Stored Procedures Part II – Signing

Well, it has been a couple of weeks since my last blog post and over a month since Maintaining Security and Performance Using Stored Procedures Part I – Using EXECUTE AS was posted, although I did spend time working on the originally unplanned follow up to that post when I would have been doing this post.  I know you all have been anxiously awaiting this post.

EXECUTE AS vs. Signing

Like EXECUTE AS, the ability to sign a module (stored procedure, DML trigger, function, or assembly) with a certificate was added in SQL Server 2005.  In addition to being able to allow access to objects within the current database context, signing also allows you to access resources in another database or that require server level permissions.  With EXECUTE AS on functions, stored procedures, and DML triggers you are limited by database context, so if you need access to objects in another database you are out of luck, even if the EXECUTE AS user has proper rights in the database.  You can download code that demonstrates this behavior here.

Demonstrations of Signing

Laurentiu Cristofor has an excellent blog post that demonstrates signing a stored procedure to grant server level permissions here, so I am not going to duplicate his work in this post.  I will demonstrate how to sign a procedure for use within a database and when accessing another database.

Using Signing to enable Dynamic SQL within the database

USE AdventureWorks;
GO

/*
Create a restricted_login
*/
CREATE LOGIN restricted_login WITH Password = '$tr0ngPassword';

GO

/*
Create a Certificate first
*/    
CREATE CERTIFICATE cert_dynamic_sql 
ENCRYPTION BY PASSWORD = 'c3rtificatePa$$w0rd'
WITH subject = 'Dynamic SQL Security'   
GO 

/* 
Create user based on the certificate
*/ 
CREATE USER certificate_user FROM CERTIFICATE cert_dynamic_sql;

/*
Give the certificate_user select on all objects
*/
GRANT SELECT ON SCHEMA::Person TO certificate_user;
GO

/*
Create a restricted rights database user
*/    
CREATE USER restricted_user FROM LOGIN restricted_login;

GO

/*
Create the procedure
*/
CREATE PROCEDURE dbo.FindPhoneByName
(
@LastName nvarchar(50) = null,
@FirstName nvarchar(50) = null
)
AS   
BEGIN
 SET NOCOUNT ON;

Declare @sql_cmd nvarchar(2000),
@select nvarchar(1000),
@where nvarchar(1000),
@parameters nvarchar(1000);

 Set @parameters = N'@FirstName nvarchar(50), @LastName nvarchar(50)';

Set @select = N'Select
Title,
FirstName,
MiddleName,
LastName,
Suffix,
Phone  
From
Person.Contact';

Set @where = N' Where 1=1 '                

If @LastName is not null
Begin
Set @where = @where + N' And LastName Like @LastName + N''%'' ';
End;

If @FirstName is not null     
Begin
Set @where = @where + N' And FirstName Like @FirstName + N''%''';
End;

Set @sql_cmd = @select + @where;

Exec sys.sp_executesql @sql_cmd, @parameters, @LastName = @LastName, @FirstName = @FirstName;

Return;         
END

GO

/*
Give the restricted user exec on the proceduere
*/
GRANT EXEC ON dbo.FindPhoneByName TO restricted_user; 

GO
/*
Change context to the restricted rights user
*/
EXECUTE AS LOGIN = 'restricted_login';

GO

/*
Exec the procedure - will fail on the dynamic portion
*/
EXEC [dbo].FindPhoneByName;

GO

/* 
Return to sysadmin rights
*/
revert;

GO

/*
Sign the procedure
*/
ADD SIGNATURE TO [dbo].[FindPhoneByName]
BY CERTIFICATE cert_dynamic_sql
WITH PASSWORD = 'c3rtificatePa$$w0rd';
GO

/*
Change context to the restricted rights user
*/
EXECUTE AS LOGIN = 'restricted_login';

GO

/*
Exec the procedure - will work now
*/
EXEC [dbo].FindPhoneByName;

GO

/* 
Return to sysadmin rights
*/
revert;

GO

Using Signing to Access an Object in Another Database

In this example I’ll use a signed procedure to access a table in the Northwind database (download here) from the AdventureWorks database (I use a 2005 copy with extra data, the unmodified version is available here).  One thing I found in my testing is that you have to use a private key file in this case.  If anyone knows how to do it without the file please let me know.  Here is the code:

USE MASTER;
GO

/*
Create the restricted_login
*/
CREATE LOGIN restricted_login WITH Password = '$tr0ngPassword';

GO

USE Northwind;

GO

/*
Create the Certificate
*/    
CREATE CERTIFICATE cert_access_other_db 
ENCRYPTION BY PASSWORD = 'c3rtPa$$word'
WITH subject = 'Access Other DB'   
GO 

/* 
Backup the certificate being sure to use a Private Key
*/
BACKUP CERTIFICATE cert_access_other_db TO FILE = 'C:\Certificates\cert_access_other_db.cer'
WITH PRIVATE KEY (FILE = 'C:\Certificates\cert_access_other_db.pvk' ,
ENCRYPTION BY PASSWORD = '3ncRyptKeyPa$$word',
DECRYPTION BY PASSWORD = 'c3rtPa$$word');
GO

/*
Create the certificate user in the Northwind and give needed permissions
*/
CREATE USER certificate_user FROM CERTIFICATE cert_access_other_db;

GO

GRANT SELECT ON dbo.Categories TO certificate_user;

GO 

USE AdventureWorks;

GO

/*
Create a restricted rights database user
*/    
CREATE USER restricted_user FROM LOGIN restricted_login;

GO

/*
Create the procedure
*/
CREATE PROCEDURE [dbo].access_other_db
AS
SET NOCOUNT ON

SELECT 
SYSTEM_USER AS USERName, 
*
FROM
[Northwind].dbo.[Categories] AS C;

RETURN;    

GO

/*
Give the restricted_user execute rights on the sp
*/
GRANT EXEC ON dbo.access_other_db TO restricted_user;

GO

/*
Create the certificate in this database from the file
*/
CREATE CERTIFICATE cert_access_other_db FROM FILE = 'C:\Certificates\cert_access_other_db.cer'
WITH PRIVATE KEY (FILE = 'C:\Certificates\cert_access_other_db.pvk',
DECRYPTION BY PASSWORD = '3ncRyptKeyPa$$word', /*The password used to create the private key */
ENCRYPTION BY PASSWORD = 'D3cryptKeyPa$$word');
GO

/* 
Execute as a sysadmin - works - this is my user
*/
EXEC [dbo].[access_other_db];

GO


/*
Now execute as the restricted user 
*/
EXECUTE AS LOGIN = 'restricted_login';

GO

/*
This will fail.
*/
EXEC [dbo].[access_other_db];

GO

/*
Back to the sysadmin level
*/
Revert;

GO

/*
Sign the procedure 
*/
ADD SIGNATURE TO dbo.access_other_db
BY CERTIFICATE cert_access_other_db WITH Password = 'D3cryptKeyPa$$word'
GO

/*
Now execute as the restricted user 
*/
EXECUTE AS LOGIN = 'restricted_login';

GO

/*
This will now work.
*/
EXEC [dbo].[access_other_db];

GO

/*
Back to the sysadmin level
*/
Revert;

GO

/*
Be sure to delete the certificate and
private key when done.
*/

Summary

As you can see from this post and the previous post (or two), the SQL Server team has given you some good options when it comes to using Stored Procedures for data access and manipulation while still maintaining security.  You can use EXECUTE AS to allow cross-schema or within database access and you can use module signing to allow access to system objects or cross-database queries without specifically granting users access to the objects.

Resources

After having planned and started the post because I had not found anything outside of Books On Line, I found a few resources that covered the material as well, and I used each to help me write this post once I found them.

• I linked to Laurentiu Cristofor’s post earlier
• Erland Sommarskog has an excellent write-up on Giving Permissions through Stored Procedures which handles this subject very thoroughly as you would expect form Erland.  Erland’s post helped me get past the fact that I needed to use a private key in order to get the cross database piece working as none of my other resources did this.
• Jonathan Kehayias also answered this this forum post with an example.

In reality I could have just posted these links as these other folks covered the subject on signing thoroughly, but I decided that since I did the research and wanted to try the code myself that I’d share my experiences as well, crediting these guys for their work and hopefully sending them some traffic, however limited, from this blog.

Finally all the MY code from this post can be downloaded from here.