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 be more specific, these are statements that when generated (via adhoc or prepared statements) don't end up being parameterized (in general - only relatively simple statements can be effectively parameterized and when your statements complex, SQL Server won't parameterize them - this can be good and in some systems this can be terribly bad). But, now you know how bad... you know how much of your cache you're wasting but where is it going and are there any similarities to these statements? Should you (or can you?) bring them together to see if there is a commonality that can be leveraged?

In general, the answer is "it depends" (you knew that was coming!). However, I do have good news, you can definitely gauge what's actually going on and how many of the statements are in fact, similar. This might allow you to leverage another database option (forced parameterization) and/or determine that you really NEED stored procedures. However, that might end up being more complex as it will require rewriting the app (or at least some of the app). Again, the good news here is that you'll be able to tell which part of the app.

Take for example the following:

DBCC FREEPROCCACHE
go
SELECT * FROM dbo.Member WHERE Lastname = 'Tripp';
go
SELECT * FROM dbo.Member WHERE Lastname = 'Tripped';
go
SELECT * FROM dbo.Member WHERE Lastname = 'Tripper';
go
SELECT * FROM dbo.Member WHERE Lastname = 'Tripps';
go
SELECT * FROM dbo.Member WHERE Lastname = 'Falls';
go

SELECT cp.objtype, cp.cacheobjtype, cp.size_in_bytes, cp.refcounts, cp.usecounts, st.text --, *
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
WHERE cp.objtype IN ('Adhoc', 'Prepared')
        AND st.text LIKE '%from dbo.member%' 
        AND st.text NOT LIKE '%SELECT cp.objecttype%'
ORDER BY cp.objtype
go

objtype       cacheobjtype          size_in_bytes   refcounts  usecounts     text
Adhoc         Compiled Plan         24576               2              1                 SELECT * FROM dbo.Member WHERE Lastname = 'Falls'; 
Adhoc         Compiled Plan         24576               2              1                 SELECT * FROM dbo.Member WHERE Lastname = 'Tripps'; 
Adhoc         Compiled Plan         24576               2              1                 SELECT * FROM dbo.Member WHERE Lastname = 'Tripper'; 
Adhoc         Compiled Plan         24576               2              1                 SELECT * FROM dbo.Member WHERE Lastname = 'Tripped'; 
Adhoc         Compiled Plan         24576               2              1                 SELECT * FROM dbo.Member WHERE Lastname = 'Tripp'; 

The first thing to notice is that each of these queries has a *different* statement in the adhoc plan cache. The second is to recognize that each of these statements take 24K in the plan cache. If we were to change to "optimize for ad hoc workloads" then things might be a bit better:

But, this is not the only issue. Even though each statement will only be in the cache once and will only take 320 bytes (instead of 24K) the cumulative effect of this might be significant. So, how can you see this? If you try to aggregate over these statements then you'll only be able to use the first 40-50 characters while that works here it definitely won't work for more complex statements. Until - SQL Server 2008. In 2008, you can use the query_hash to find similar statements and their overall affect on the query cache.

This is incredibly important because this will give you the information to determine what SIMILAR query (or queries) are consuming the cache and which ones have the highest cumulative effect. And, if you see a statement that is consistent and can be optimized using indexes (and consistently using the same indexes) what you might try first is "forced parameterization." This is a DATABASE-level option (available in 2005+) that significantly improves but still doesn't guarantee that a statement will be parameterized. See the Books Online for Forced Parameterization (SQL Server 2008 Forced Parameterization and SQL Server 2005 Forced Parameterization) for the rules on when SQL Server parameterizes. However, there are many statements that will. If you have A LOT of plan cache bloat AND you think your plans might benefit from forced parameterization - this is definitely something you can try.

Again, the good news here is that the QP will try harder to parameterize but there are still many cases where it won't. In this case, SQL Server parameterizes this statement (it becomes a Prepared statement) as:

(@0 varchar(8000))select * from dbo . Member where Lastname = @0

You can see this through the following query/results:

Ideally, you'll end up with less wasted cache because all adhoc statements from here - will use the Prepared version of the statement. This also means that they'll run with a compiled plan (so you'll also save compile time) and - if the plans are consistent then all of this is good. However, if other values (for example 'Smith') require a completely different plan - then this could actually be worse (this is the "It Depends" part). But, if you still have statements that do have consistent execution plans (because you know the data and/or you know exactly how this particular data is being used) then EITHER sp_executesql or a stored procedure would effectively force the parameterization as well and it would say to me that you really understand your data/application. But, if you're wrong... anytime you force the parameterize (and increase re-use of a plan - you could end up forcing the re-use of a bad (and possibly REALLY, REALLY, REALLY, REALLY, REALLY, REALLY, REALLY, REALLY, REALLY bad plan) :)).

So, while you solve one problem - you could create another one (see the blog post titled: Using the OPTION (RECOMPILE) option for a statement for more information). If the plans are NOT consistent then forcing a plan (through either forced parameterization, sp_executesql OR a regular stored procedure) can result in poor performance. When writing the stored procedure (and through good testing processes), you'll be able to create better and more effective code. This is truly my preferred method. And, I'm just about to wrap up my series with roughly 3 more posts (at least that's what I currently have planned but you guys have certainly been steering me in this direction for a bit :-)).

Thanks for reading,
kt

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 and tricks using these commands.

First off - could we have helped the performance of the sp_executesql statement?

Yes...

If we know that a statement returns a varying amount of data (based on the parameters supplied) then we can use the SQL Server 2005 feature WITH RECOMPILE to tell SQL Server that the statement being executed should have it's own plan created and that prior plans (if they exist) should not reuse the statement. It also tells SQL Server that this particular plan is a "single-use plan" that should not be reused for subsequent users. To see the combination of all of these things - I'll use some of the DMVs that track plan cache and plan utilization.

Right now, this query returns 0 rows.

I'll execute the following and then recheck the plan cache:

Now, we have a row for our parameterized query plan:

text                                                                                                                                                            EXECUTION_COUNT
(@lastname varchar(15))SELECT * FROM dbo.member WHERE lastname LIKE @lastname              1

So, what is this showing us... it's showing us that there's a plan in cache for this statement. And, if we're interested in seeing the plan, we can remove the commented out cp.* in the query above to get the cp.query_plan column. It shows the following:

<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.1" ...LOTS OF OTHER STUFF THAT I... </ShowPlanXML>  (here's the FULL text of this: query plan xml.sqlplan (11.98 kb))

OK, I know that's not overly helpful... but, you're more than welcome to copy it and save it as a .sqlplan file. Once saved as a .sqlplan file - you can open it directly into SQL Server 2005 or SQL Server 2008's Management Studio. However, the very *nice* feature of SQL Server 2008's Management Studio is that a single click on an XML showplan - will go DIRECTLY into a graphical query plan window:

 

And, once again, we see the optimal plan (to use the index and do a bookmark lookup) because this query is highly selective (only 1 row).

We'll execute the exact same statement again - using the value of Anderson just to get setup to the point where we were last week:

DECLARE @ExecStr nvarchar(4000)
SELECT @ExecStr = 'SELECT * FROM dbo.member WHERE lastname LIKE @lastname'
EXEC sp_executesql @ExecStr, N'@lastname varchar(15)', 'Anderson'
go

And, we see that it uses the EXACT same plan (looking at showplan). In fact, we can see that from checking our plan cache as well:

text                                                                                                                                                            EXECUTION_COUNT
(@lastname varchar(15))SELECT * FROM dbo.member WHERE lastname LIKE @lastname              2

And, while we know that this plan (to use the index) is good for the highly selective value of 'Tripp' it's is not good for the value of Anderson as there are many rows that match. If we suspected this and/or knew this when we were executing (from the client) then we could use OPTION (RECOMPILE) to force SQL Server to get a new plan:

DECLARE @ExecStr nvarchar(4000)
SELECT @ExecStr = 'SELECT * FROM dbo.member WHERE lastname LIKE @lastname OPTION (RECOMPILE)'
EXEC sp_executesql @ExecStr, N'@lastname varchar(15)', 'Anderson'
go

The query plan as a result of this execution is:

And, this is the more optimal plan given this parameter. At ths point, the questions (IMO) are:

1. Would someone really be able to programmatically guestimate that a parameter submitted from the client is "atypical" and/or that it warrants recompilation? And, I guess I can answer yes - for some parameters - like those with a wildcard. But, if we're just talking about two different values against a single column, this would be guessing statistics of the data.
2. Should this particular statement always be executed to recompile and never save a plan?
3. What did SQL Server do with the plan itself?

I'll go with answering #3 first as that one is easy to answer. Using the same statement, I'll again query the plan cache:

text                                                                                                                                                            EXECUTION_COUNT
(@lastname varchar(15))SELECT * FROM dbo.member WHERE lastname LIKE @lastname              2

Even though this is the third time we have executed this statement, this final execution was NOT put in cache. It was used solely for the execution with OPTION (RECOMPILE). And, it will NOT affect future executions. If I go back and execute without the OPTION (RECOMPILE) then I will get the prior plan (to use the index).

Now, the other two questions - these are a lot more interesting and this is where I think that stored procedures should be used. Personally, I think that developers that know the data and know the application - will be a lot better at creating the RIGHT code especially when they understand all of the options available to them.

Here's the way I think about stored procedures and recompilation:

• If I know that a particular statement always returns the same number of rows and uses the same plan (and, I'd know this from testing), then I'll create the stored procedure normally and let the plan get cached.
• If I know that a particular statement wildly varies from execution to execution and the optimal plan varies (again, I should know this from testing multiple sample executions), then I'll create the stored procedure normally and I'll use OPTION (RECOMPILE) to make sure that the statement's plan is not cached or saved with the stored procedure. On each execution that stored procedure will get different parameters and the particularly nasty statement will get a new plan on each execution.

However, this is also where things get more challenging. I've often seen stored procedures where people try and use conditional logic to break up the different types of parameters and this doesn't usually work out as well as expected (I'll blog this next). And, this is always where some decide that they want to dynamically build the statement that gets executed - now, we need to determine whether or not we should use sp_executesql or EXEC. And, there are really a couple of options at this point. Ultimately, in one or two more posts - I'll finally show you where EXEC is a clear winner over sp_executesql because even the OPTION (RECOMPILE) doesn't always help ALL kinds of plans (and especially one of the more common types of plans I see).

So, I'm getting closer... at least two more to go here. Thanks for the great questions/comments!
kt

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 performance perspective. So, to really show this, I'm going to start with focusing on the similarities and differences of EXEC and sp_executesql. I'll start here with some performance details but you'll find that I'm going to just hit the tip of the iceberg with this one. I'll definitely need another post or two!

First, a quick overview:

sp_executesql (also known as "Forced Statement Caching")

• Allows for statements to be parameterized
• Only allows parameters where SQL Server would normally allow parameters; however, this string can be built using forms of dynamic constructs. I'll give more details on this in additional posts.
• Has strongly typed variables/parameters - and this can reduce injection and offer some performance benefits!
• Creates a plan on first execution (similar to stored procedures) and subsequent executions reuse this plan

EXEC  (also known as "Dynamic String Execution" or DSE)

• Allows *any* construct to be built
• Treats the statement similarly to an adhoc statement. This means that the statement goes through the same process that adHoc statements do - they are parsed, probably parameterized and possibly deemed "safe" for subsequent executions to re-use
• Does not have strongly typed parameters in the adhoc statement and therefore can cause problems when the statements are executed (I have ways around this)
• Does not force a plan to be cached.
  • This can be a pro in that SQL Server can create a plan for each execution
  • This can be a con in the SQL Server needs to recompile/optimize for each execution

Let's start with using sp_executesql to parameterize a query where SQL Server would also allow parameters:

Because 'Tripp' is a highly selective name, SQL Server uses an index to the lookup of the data:

So, for our next execution, I'll supply a different lastname - a lastname of Anderson. In this database, Anderson is NOT highly selective:

DECLARE @ExecStr nvarchar(4000)
SELECT @ExecStr = 'SELECT * FROM dbo.member WHERE lastname LIKE @lastname'
EXEC sp_executesql @ExecStr, N'@lastname varchar(15)', 'Anderson'
go

However, the query plan looks exactly the same:

Or does it? It turns out that this query plan does look a tiny bit different but it's not very obvious... it's in the thickness of the lines. In this second execution it clearly shows that more data is being passed between the steps. But, is this a bad thing? Maybe, maybe not. Let's drill in a bit deeper. If I hover over the Index Seek (on member.test), I can see the following tooltip:

The key point here is that it shows an "Estimated Number of Rows" of 1.96 but an "Actual Number of Rows" of 385. That's pretty far off... why? Because this statement's plan was determined by the first execution of sp_executesql. Let's try another execution:

Because the query has LIKE in it, we can use wildcards. And, let's do that! This time I'll supply a wildcard of %e%:

DECLARE @ExecStr nvarchar(4000)
SELECT @ExecStr = 'SELECT * FROM dbo.member WHERE lastname LIKE @lastname'
EXEC sp_executesql @ExecStr, N'@lastname varchar(15)', '%e%'
go

And, again, the query plan looks the same... but with even thicker lines. Yes, I realize... this is not blindingly obvious:

And, by turning on SET STATISTICS IO ON, we can also review the [Logical] IOs performed:

For 'Tripp' 

Table 'member'. Scan count 1, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

For 'Anderson'

Table 'member'. Scan count 1, logical reads 772, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

For '%e%'

Table 'member'. Scan count 1, logical reads 10019, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

This table only has 144 pages. In the cases of 'Anderson' and '%e%', SQL Server would have been better off doing a table scan. But, because I used sp_executesql I forced SQL Server to do caching. Subsequent users use the plan whether it's optimal or not.

And, there are even more complicated scenarios than this. I plan to keep tackling these issues over the next few days and I'll add quite a bit more to this. However, it's late. So, I'll dangle the carrot for now. Within the next couple of days, I'll show a series of EXEC statements that generate both SAFE and UNSAFE plans.

Thanks for reading!
kt

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 and important reasons to use DSE and some are performance related (ok, this is another post for another day) but suffice it to say - I use DSE but I also know how to prevent Little Bobby Tables from running amuk within my database.

(April 5 note: after a few comments about sp_executesql, I've modified the following paragraph to explain a bit more... I'm *very* aware of sp_executesql and while it does have some benefits, it also has some negatives! I promise, this is coming soon in another post. I really want this post to focus solely on how to minimize injection when you do NEED to use a dynamically constructed string and there are times when this is the ONLY way to solve certain performance problems. sp_executesql - while often compared to EXEC as a better choice just isn't ALWAYS better and really it's not a simple comparison as they are not equivalent!! In fact, there are some cases where I would specifically avoid sp_executesql... So, I promise... this will be coming up in a blog post soon!! Great comments! Thanks!! :))

So, while the discussion on when, where and why you might use DSE is another one - especially when compared with sp_executesql (yes, I hear another blog post coming - and, well, I'm kind of on a roll right now :)... I want to give you a couple of very cool tips/tricks to reduce the possibilities of problems if/when you do use it (and, there REALLY are places where it's necessary!). It's especially important as there are a few things that many folks just don't even know exist. And, there are 3 parts to this article... I would expect that most apps need QUOTENAME() and/or REPLACE() but ALL apps should consider my recommendations/comments for EXECUTE AS - if/when you're actually using DSE (through EXEC).

• QUOTENAME()
• REPLACE()
• EXECUTE AS

Part I: QUOTENAME()

In a question/thread (this is many moons ago), the following question was asked (NOTE: I have "tweaked" the code to highlight ONLY the problem here AND I've made it so that it won't execute):

I have a procedure that creates a database (below). When people use spaces in their database names it fails. How can I make it work if they supply spaces?

CREATE PROCEDURE dbo.CreateDBProc
(
     @DBName sysname
)
AS
DECLARE @ExecStr nvarchar(2000)
SELECT @ExecStr = N'CREATE DATABASE ' + @DBName -- + all of the other stuff to place the files, etc...
SELECT @ExecStr
--EXEC(@ExecStr)
go

And, there was a reply to this question where the answer was (and I am NOT recommending this answer. Please do NOT stop reading here otherwise you are asking for a world of pain - and LOTS of SQL Injection):

Just go ahead and do the following. By putting brackets around the name you will solve this problem.

CREATE PROCEDURE dbo.CreateDBProc
(
     @DBName sysname
)
AS
DECLARE @ExecStr nvarchar(2000)
SELECT @ExecStr = N'CREATE DATABASE [' + @DBName + N']' -- + all of the other stuff to place the files, etc...
SELECT @ExecStr
--EXEC(@ExecStr)
go

OK, this is a SCARY recommendation. Not just because it won't always work (what if someone actually wants [don't get me wrong - I don't know WHY they'd want to do this but...] to put [brackets] in the name?) but because it's prone to SQL Injection.

This won't work:

And, this is just scary what I can do:

So, what should we do?

ANSWER: If you're building strings where identifiers are parameterized (and, there are many reasons for why this many not be the best idea - but, if you absolutely must), USE QUOTENAME()!

So, here's the correct way to code this procedure so that an identifier cannot be subjected to SQL Injection:

Now, no matter what messed up name someone actually WANTS to create - this will protect it such that SQL Injection is not possible. You can try any of these combinations with this version of the proc and while creating a database actually named "[fakedbname] DROP DATABASE foo --" probably isn't desirable, dropping a database isn't either (and I would argue is MUCH worse).

All of these executions are "protected" from SQL Injection:

Note: The first and second execute (if you actually execute instead of just SELECT @ExecStr) *and* create databases with these ugly names. The third and fourth don't execute because some of the injected characters are not supported for file/directory names. However, the most important point is that *all* are protected from SQL Injection!

Part II: REPLACE()

But, what if the string you need to work with isn't an identifier? Can you still use QUOTENAME()? And, the answer is YES, IF the string is compatible with SYSNAME which is an nvarchar(128). However, if you need longer strings or if you are protecting a string which is not an identifier - what else can you do? Well... it's definitely NOT as pretty but it works. The answer here is to use REPLACE. If I had wanted to do this with the procedure above, I would have had to do the following:

However, this will NOT work here because an identifier must be properly quoted within the string and there are only 2 ways to "quote" identifiers. One is the [bracket] and the other is "double quotes" not single. Instead, this is what the procedure will look like (nope! we don't need REPLACE):

CREATE PROCEDURE dbo.CreateDBProcQuotes
(
      @DBName sysname
)
AS
DECLARE @ExecStr nvarchar(2000)
SELECT @ExecStr = N'CREATE DATABASE [' + @DBName + N']'-- + all of the other stuff to place the files, etc...
SELECT @ExecStr
--EXEC(@ExecStr)
go

And, SQL only supports brackets by default (without any special settings). To allow double quotes, you can turn on QUOTED_IDENTIFIER (SET QUOTED_IDENTIFIER ON) to allow double quotes instead of brackets but this has a few negative consequences (See SET Options That Affect Results) and personally, I always try to avoid making SET options changes in most of my stored procs.

CREATE PROCEDURE dbo.CreateDBProcQuotes
(
      @DBName sysname
)
AS
SET QUOTED_IDENTIFIER ON
DECLARE @ExecStr nvarchar(2000)
SELECT @DBName = REPLACE(@DBName, N'''', '''''')
SELECT @ExecStr = N'CREATE DATABASE "' + @DBName + N'"'-- + all of the other stuff to place the files, etc...
SELECT @ExecStr
--EXEC(@ExecStr)
go

Once again, REPLACE isn't needed. And, to be honest, this becomes a nightmare with " [double quotes] or actual [brackets] in the name. QUOTENAME() deals with those properly. So, with an identifier you really need QUOTENAME(). So, where does replace come in? With actual strings in your DSE. Imagine that you're wanting to pass in a first name but something about your application requires that this happen through DSE (and, this CAN definitely happen - mostly for performance purposes). Don't get me wrong, I just don't have the ability to do all of this within one post but simply put, because of how stored procedures create their execution plans there are OFTEN reasons for why you might want a string to be built dynamically. No, probably not one quite this simple but I'm trying to keep this particular example simple. This is probably what it would look like to start:

And, on first test:

And, on first test it generates: SELECT * FROM dbo.member WHERE FirstName = Kimberly which gives you an error because the firstname isn't properly quoted. So, you think... oh, I need to quote these in my EXEC statement:  

Ah, that works... for awhile. Then, someone complains that it doesn't work for C'Anne (I just met someone named C'Anne - pronounced SeaAnne) this past week.

And, it generates:

SELECT * FROM dbo.member WHERE FirstName = 'C'Anne'

which gives you a syntax error.

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'Anne'.
Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string ''.

Darn. But, at least this is how you catch it. Instead of with Little Bobby Tables. What if this gets executed:

Luckily, I got:

And, this requires that the string be surrounded with quotes AND the string submited have all single quotes replaced with '' [single quote followed immediately by a second single quote]). Yes, I realize that this is starting to look ugly but... what happens with Little Bobby Tables? We get his row. NOTE: We do have other potential performance problems here because the parameter (when it gets thrown back out into the string - is no longer an NVARCHAR, it's only a varchar). So, we should also consider adding an N into the string like the following:

SELECT @ExecStr = N'SELECT * FROM dbo.member WHERE FirstName = N''' + REPLACE(@FirstName, N'''', N'''''') + N''''

And, what if the string being built has numerics or other types - what should we do there? To be honest, I often EXPLICITLY type the variable in what looks like a completely unnecessary CONVERT. However, for performance purposes I *often* do this. Ugh, the future posts are REALLY starting to build from here! Again, I'm not hitting performance in this one so I'm not going to take the bait of the tangent. Another time for sure!

Finally, what if our strings are REALLY complex and we need to do some strange stuff and well... we're not sure that the string can be completely protected? What if we're passing in a much more complicated "piece" of a WHERE clause or a full and customized ORDER BY? What if it's not just a SINGLE value? Ugh... this is much harder but you definitely need EXECUTE AS.

Part III: EXECUTE AS

This is the ultimate in protection for your DSE code. This is where you decide that you're going to allow virtually anything in the WHERE clause or ORDER by but you really want to protect your database. Here, what I need is EXECUTE AS. And I'm not a big fan of this kind of code - but, again, I'm not looking at perf or anything here... I'm *just* looking at the security and potential of the injection.

SQL Server 2005 introduced EXECUTE AS and it has a lot of options - in fact, 4 options:

• EXECUTE AS Caller
• EXECUTE AS User
• EXECUTE AS Self
• EXECUTE AS Owner

EXECUTE AS Caller is the default. This means that the user that executes the procedure must have the DIRECT rights associated with the Dynamically Executed String. And, so, this is good. This should protect MOST cases altogether. However, what if the users have higher privileges? Well, I'd say that there's already something wrong with your database (because this should NEVER be the case) but... well... I've seen apps (and even RECENTLY) heard of apps that connect as SA. Yes, seriously. So... what can they do... Well, much worse that DROP TABLE. How about DROP DATABASE. Or, well, let's just say that if I found vulnerabilities in that - I could possible get outside of their SQL Server and do even more damage - to files, to the network. So... this is REALLY REALLY REALLY REALLY BAD. I would STRONGLY suggest that anyone that connects as SA - IMMEDIATELY WORK HARD TO CHANGE THIS.

EXECUTE AS Owner is almost as bad (although nothing's as bad as connecting as SA) - especially when the OWNER has elevated rights. So, I'd try to avoid EXECUTE AS Owner unless you SEVERELY restrict who has permissions to the procedure in general. But, don't EXECUTE AS Owner for a stored procedure that's granted EXEC to public. That completely defeats the purpose!

EXECUTE AS Self is a bit strange. But, when a stored procedure is created in a schema, the ownership chaining and privileges follow from the OWNER of the schema. So, what if the author of the stored procedure has rights that the owner doesn't. No, I don't really recommend this but that's where "self" comes in. This is where the procedure can execute under the context of the actual creator (not the owner). However, often these are the same and often they're DBO. This is even worse... Although still, not as bad as connecting as SA.

EXECUTE AS User (where the User is a low-privileged user with very few rights) is good. And, in fact, this will be the answer to this problem!

Yes, even if you connect as SA, I can STILL protect you. Yes, we can still protect this code even when run under the connection context of SA - with EXECUTE AS.

And, even if for some reason you can't use REPLACE() this procedure will be limited in what they can do because the user (User_procedurename) has limited rights within the database. In fact, I would recommend that the user ONLY have the necessary rights for THAT stored procedure and I'd even recommend that you create one user for each stored procedure (that has DSE). Yes, that's potentially a lot of users... but, better than the alternative(s)!

Summary/Resources

Finally, please be sure that you're doing some form of auditing if you're using EXECUTE AS. And, you should also do a quick check to see if anyone is ELEVATING user's rights to a more privileged user. Check out these two blog posts for additional information:

• "EXECUTE AS" and an important update your DDL Triggers (for auditing or prevention)
  • This one will show you how you can audit for the actual context of the connection not just the context under which something executed.
• Looking for security vulnerabilities in database code
  • This one will help you find all of your procedures that use EXECUTE AS.

And... ah... I'm done! There's a lot here but a lot of tips. You CAN prevent SQL Injection.

Thanks for reading!
kt

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 fantasic solution to protectng identifiers as input parameters but it can't protect more complex strings).

Having said that, what if I'm looking at a database for the first time... just poking around trying to see if there's anything that needs further attention? I've come up with a quick query... And, while it's not going to "solve" your problem (as that's going to take some re-writing of code) or even truly verify if you're vulnerable, it gives you a "quick list" of where you should look first! If your code uses dynamic strings AND it's elevated - then start there! 

Is this enough? Anything else you'd check? What do you think?

THANKS!
kt

Having said that, and since security is always a concern, my DDL Trigger only audited for the login of the user who executed the statement, not for the actual user that’s logged in. In other words, if EXECUTE AS is used (or SETUSER is used), then the context of the user executing is actually different then the logged in user. To see this shift in context, SQL Server 2005 added a new function: ORIGINAL_LOGIN().

(reading between the lines is even more frightening in that prior to SQL Server 2005, the original user could not be tracked from SETUSER. The good news is that SETUSER is ONLY allowed to be used by DBOs so it’s not as widespread as the potential for “execute as”).OK, so how can we put all of this together? We’ll want to add the ORIGINAL_LOGIN function into our audit table in our DDL Trigger. Even if you choose NOT to rollback, at least you’ll know who performed the operation (even if from a dynamically executed string!).

USE AdventureWorks;
go

--Create a login/user - just for this exercise

CREATE LOGIN Paul WITH PASSWORD = 'PxKoJ29!07';
go

CREATE USER Paul FOR LOGIN Paul;
go

sp_addrolemember 'db_ddladmin', 'Paul'
go 

CREATE SCHEMA SecurityAdministration
go

CREATE TABLE SecurityAdministration.AuditDDLOperations
(            OpID                int               NOT NULL identity
                                                       CONSTRAINT AuditDDLOperationsPK
                                                           PRIMARY KEY CLUSTERED,
            OriginalLoginName    sysname           NOT NULL,
            LoginName            sysname           NOT NULL,
            UserName             sysname           NOT NULL,
            PostTime             datetime          NOT NULL,
            EventType            nvarchar(100)     NOT NULL,
            DDLOp                nvarchar(2000)    NOT NULL
);
go

GRANT INSERT ON SecurityAdministration.AuditDDLOperations TO public;
go

CREATE TRIGGER PreventAllDDL
ON DATABASE
WITH ENCRYPTION
FOR DDL_DATABASE_LEVEL_EVENTS
AS
DECLARE @data XML
SET @data = EVENTDATA()
RAISERROR ('DDL Operations are prohibited on this production database. Please contact ITOperations for proper policies and change control procedures.', 16, -1)
ROLLBACK
INSERT SecurityAdministration.AuditDDLOperations
                        (OriginalLoginName,
                         LoginName,
                         UserName,
                         PostTime,
                         EventType,
                         DDLOp)
VALUES   (ORIGINAL_LOGIN(), SYSTEM_USER, CURRENT_USER, GETDATE(),
   @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
   @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)') )
RETURN;
go 

--Test the trigger.

CREATE TABLE TestTable (col1 int);
go

DROP TABLE SecurityAdministration.AuditDDLOperations;
go

EXECUTE AS LOGIN = 'Paul' -- note: Remember, Paul is a DDL_admin
go

DROP TABLE SecurityAdministration.AuditDDLOperations;
go

REVERT;
go 

SELECT * FROM SecurityAdministration.AuditDDLOperations;
go

DROP TRIGGER PreventAllDDL ON DATABASE;
go

DROP TABLE SecurityAdministration.AuditDDLOperations;
go

DROP SCHEMA SecurityAdministration;
go

DROP USER Paul;
go 

DROP LOGIN Paul;
go 

So, have fun testing with this one. 

Thanks for reading!
kt