Couple of useful new features

We can now INSERT INTO EXEC(...) into a table variable. We are no longer limited to using a temp table for this. Sorry, but, I like this new feature very much. Although temporary table storage is rather sloppy (and temporary tables are more sloppy than table variables), catching the results from xp_cmdshell is one seemingly legitimate use for them.

Also stumbled across the WITH EXECUTE AS clause. I was using some kind of stored procedure wizard thing (IDE folks are sure to love it) to make a stored procedure that provides directory listings using the new INSERT INTO EXEC... functionality. It executes the stored procedure (or queue, or trigger, or function) with the specified permissions. I have no idea what a queue is, beyond the obvious, 'it's a queue'; I'll have to dig that one up. At any rate, declare your procedure as normal...

CREATE PROCEDURE <owner>.<procedure>
@parm0 int
@parm1 int
/* with clause comes after parameter list */
WITH EXECUTE AS < CALLER | SELF | OWNER | 'user_name' >
AS ...

When the procedure is executed, the permissions of the specified executor are used when accessing all required base objects.
AS OWNER, the procedure is executed with the permissions of the procedure owner (dbo is common)
AS SELF executes the procedure with the permissions of the user that created the procedure
AS CALLER executes the procedure with the permissions of the process/user who is calling it
AS 'user_name' can be any valid user; the procedure will be executed with that user's permissions.

This will be handy when ownership chaining causes problems. Instead of the caller having to have permissions on the various base tables (messy bit of work, that is), the procedure can be executed as the owner, or any other use with the appropriate permissions. Let's see how long till there is an escalation exploit for this one :-)


Wow! schemas are no longer directly tied to database users (e.g., a table owner no longer has to be a user). Users can be managed without worrying about the effect on the objects they may have owned. I'll have to read up on this one.