\nDynamic 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.\n<\/p>\n
\nSo 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:\n<\/p>\n
\npublic static void GetAuthorsNET {
\nusing (SqlConnection conn = new SqlConnection("context connection=true"))
\nusing (SqlCommand cmd = new SqlCommand("select * from dbo.authors", conn)
\n{
\n conn.Open();
\n SqlContext.Pipe.ExecuteAndSend(cmd);
\n}
\n}\n<\/p>\n
\nGRANT someuser EXECUTE on dbo.GetAuthorsNET\n<\/p>\n
\nEXECUTE AS USER='someuser'
\nEXEC dbo.GetAuthorsNET
\nGO
\nREVERT\n<\/p>\n
\nexecutes 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:\n<\/p>\n
\nCREATE PROCEDURE dbo.GetAuthorsSQL
\nAS
\nSELECT * FROM dbo.authors\n<\/p>\n
\nGRANT someuser EXECUTE on dbo.GetAuthorsSQL\n<\/p>\n
\nwould execute the SELECT as the OWNER of the stored procedure, not the caller and the SELECT succeeds.\n<\/p>\n
\nOK. How about the following .NET code? Does it execute dbo.byroyalty as the OWNER of the stored procedure?\n<\/p>\n
\npublic static void ExecByRoyalty {
\nusing (SqlConnection conn = new SqlConnection("context connection=true"))
\nusing (SqlCommand cmd = new SqlCommand("dbo.byroyalty", conn)
\n{
\n cmd.CommandType = CommandType.StoredProcedure;
\n cmd.Parameters.AddWithValue("@percentage", 50);
\n conn.Open();
\n SqlContext.Pipe.ExecuteAndSend(cmd);
\n}
\n}\n<\/p>\n
\nGRANT someuser EXECUTE on dbo.ExecByRoyalty — grant on calling proc (owner by dbo)
\nDENY someuser EXECUTE on dbo.ByRoyalty — deny on called proc\n<\/p>\n
\nThis executes the ByRoyalty proc AS the OWNER of the ExecByRoyalty proc, ownership chain intact. Even if someuser executes ExecByRoyalty. Oh.\n<\/p>\n
\nTime for a tiebreaker. How about this one?\n<\/p>\n
\npublic static void ExecByRoyaltyAsString {
\nusing (SqlConnection conn = new SqlConnection("context connection=true"))
\nusing (SqlCommand cmd = new SqlCommand("exec dbo.byroyalty @perc", conn)
\n{
\n cmd.CommandType = CommandType.Text; \/\/ not a sproc, a textual execute statement, does it matter?
\n cmd.Parameters.AddWithValue("@perc", 50);
\n conn.Open();
\n SqlContext.Pipe.ExecuteAndSend(cmd);
\n}
\n}\n<\/p>\n
\nThis executes the ByRoyalty proc AS the OWNER of the ExecByRoyaltyAsString proc, ownership chain intact. The sproc works even when 'someuser' executes it. Interesting.<\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[23,38],"tags":[],"class_list":["post-879","post","type-post","status-publish","format-standard","hentry","category-security","category-sqlclr"],"yoast_head":"\n