OK, I've had a bit of a tangent going on optimizing stored procedures and its all revolved around adhoc plan caching and potentially wasted cache due to the single-use plans created (see the most recent posts titled:Plan cache and optimizing for adhoc workloads and Plan cache, adhoc workloads and clearing the single-use plan cache bloat). To [...]
Kimberly L. Tripp
Improving my SQL skills through your questions!
Clearing the cache – are there other options?
Posted on: April 28, 2010 12:20 pm
Using the OPTION (RECOMPILE) option for a statement
Posted on: April 15, 2010 1:28 pm
I started this series with the post titled: Little Bobby Tables, SQL Injection and EXECUTE AS. I then moved to discussing some of the differences with the post titled: EXEC and sp_executesql – how are they different? Today, I want to address a few of the comments as well as continue with a few tips [...]
EXEC and sp_executesql – how are they different?
Posted on: April 5, 2010 9:51 pm
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
Posted on: April 4, 2010 4:42 pm
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
Posted on: June 30, 2009 8:34 am
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)
Posted on: July 24, 2007 1:31 pm
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, [...]
