Dynamic SQL executed in a stored procedure executes by default using the security context of the CALLER of the procedure rather than the OWNER. That's the way SQL Server has always worked, and although SQL Server 2005 lets you EXECUTE AS OWNER (among other choices), EXECUTE AS CALLER is still the default.
So how does this relate to SQL statements execute in a .NET stored procedure, function, or trigger? Do these execute as caller or owner? Turns out it depends on what statement you are executing. Executing an "ordinary" SQL statement like this:
public static void GetAuthorsNET {
using (SqlConnection conn = new SqlConnection("context connection=true"))
using (SqlCommand cmd = new SqlCommand("select * from dbo.authors", conn)
{
conn.Open();
SqlContext.Pipe.ExecuteAndSend(cmd);
}
}
GRANT someuser EXECUTE on dbo.GetAuthorsNET
EXECUTE AS USER='someuser'
EXEC dbo.GetAuthorsNET
GO
REVERT
executes the SELECT statement in the .NET code as CALLER and throws a permission denied error if the caller doesn't have direct SELECT access to the authors table. The same contruct in a T-SQL procedure:
CREATE PROCEDURE dbo.GetAuthorsSQL
AS
SELECT * FROM dbo.authors
GRANT someuser EXECUTE on dbo.GetAuthorsSQL
would execute the SELECT as the OWNER of the stored procedure, not the caller and the SELECT succeeds.
OK. How about the following .NET code? Does it execute dbo.byroyalty as the OWNER of the stored procedure?
public static void ExecByRoyalty {
using (SqlConnection conn = new SqlConnection("context connection=true"))
using (SqlCommand cmd = new SqlCommand("dbo.byroyalty", conn)
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@percentage", 50);
conn.Open();
SqlContext.Pipe.ExecuteAndSend(cmd);
}
}
GRANT someuser EXECUTE on dbo.ExecByRoyalty — grant on calling proc (owner by dbo)
DENY someuser EXECUTE on dbo.ByRoyalty — deny on called proc
This executes the ByRoyalty proc AS the OWNER of the ExecByRoyalty proc, ownership chain intact. Even if someuser executes ExecByRoyalty. Oh.
Time for a tiebreaker. How about this one?
public static void ExecByRoyaltyAsString {
using (SqlConnection conn = new SqlConnection("context connection=true"))
using (SqlCommand cmd = new SqlCommand("exec dbo.byroyalty @perc", conn)
{
cmd.CommandType = CommandType.Text; // not a sproc, a textual execute statement, does it matter?
cmd.Parameters.AddWithValue("@perc", 50);
conn.Open();
SqlContext.Pipe.ExecuteAndSend(cmd);
}
}
This executes the ByRoyalty proc AS the OWNER of the ExecByRoyaltyAsString proc, ownership chain intact. The sproc works even when 'someuser' executes it. Interesting.