Statement execution and why you should use stored procedures

OK, so, this is where we're at… When a client application makes a request to SQL Server there are a few ways in which they can do it: Submit an adhoc transact-SQL statement This statement can be parameterized and the parameterized statement can be deemed: Safe: These are statements where the optimizer has evaluated the […]

Microsoft eLearning Resources – Developer Content – 2008 & 2008 R2

In addition to the SQL Server 2008 Database Infrastructure and Scalability content that Paul and I delivered for SQL Server 2008, our colleague Bob Beachemin (blog) also delivered complementary content under the Developer track. This course is officially titled and available as: Clinic 10164: Essential SQL Server 2008 for Developers. Additionally, this content has been updated […]

Microsoft eLearning Resources – Clinic 10259 (SQL 2008 DBIS)

When SQL Server 2008 was in beta, Paul and I worked on a project to create content that would "jumpstart" folks in learning and working with SQL Server 2008. We delivered these "new features" courses at various locations and finally, we recorded the content. All of this content is now available FOR FREE from Microsoft […]

EXEC and sp_executesql – how are they different?

In my last post: Little Bobby Tables, SQL Injection and EXECUTE AS, I wanted to highlight how to avoid SQL Injection when using EXEC. A few people brought up the point that I could have avoided SQL Injection by using sp_executesql. And, while sp_executesql can be used in some cases, it’s not always the most ideal from a […]

Little Bobby Tables, SQL Injection and EXECUTE AS

OK, I know many of you have seen this before (an oldie, but a goodie!): (image from xkcd.com, with “copy and share” license described here: License) But, what can you do to prevent this? And, when would this even be possible? This is possible when DSE (dynamic string execution) occurs. There are still some VERY relevant […]

Looking for security vulnerabilities in database code

I've always been concerned with security and I've always stressed the importance of auditing the REAL user context not just the current user (see this post on EXECUTE AS and auditing). So, I generally try to avoid using dynamic string execution and if necessary create well tested/protected parameters (fyi – using QUOTENAME can be a […]

“EXECUTE AS” and an important update your DDL Triggers (for auditing or prevention)

DDL Triggers were a new feature of SQL Server 2005 and while seemingly simple, they are very powerful. DDL Triggers allow you to trap an attempted DDL operation to audit it, prevent it, or do anything you want to validate/verify/”authorize”/etc – you write the code. And, since a trigger fires as part of the transaction, […]

Instant Initialization – What, Why and How?

Instant Initialization is a new feature of SQL Server 2005 that is based on an NTFS feature that was added to Windows XP (and therefore is also available in Windows 2003 Server). It's a feature that's seemingly simple; it allows file allocation requests to skip zero initialization on creation. As a result, file allocation requests […]

Security Best Practices

Today Michele and I were chatting about Security. She’s focused a lot on it these days and is chatting with everyone about their best practices… I thought I’d mention a few important links/ideas here: Login Mode: Allow windows authentication only – making sure that the sa account has a strong password set (regardless of the […]

Stored Procedures are NOT evil… but they can be frustrating!

After a group of RDs started chatting about stored procedures I finally felt like I could join in. Often they talk about .Net and client stuff and well, quite honestly, I don’t care what you do with the data…only that I serve it up quickly. Ok, I’m really kidding here but my primary focus is […]