SQL 'Execute As' Login Command and Linq to SQL

Question

I am trying to execute a sql query as another login using the 'Execute As' command. I am using Linq to SQL, so I've generated a Data Context class and I am using the ExecuteQuery method to run the 'Execute As' SQL command. I then call a Linq to SQL command that is successful. However, every subsequent query fails with the following error:

A severe error occurred on the current command. The results, if any, should be discarded.

Here is the code snippet that I have tried:

SummaryDataContext summary = new SummaryDataContext();
summary.ExecuteQuery<CustomPostResult>(@"Execute as Login='Titan\Administrator'");
var test = summary.Customers.First();
var test2 = summary.Products.ToList();

No matter what query I run on the second query I receive the error message from above. Any help would be appreciated.

Answer 1

You may have already ruled this out, but one possible work around would be to simply create the data context with a different connection string.

To edit the connection string, you can set the DataContext.Connection.ConnectionString property. I've done it before in the partial method OnCreated(), which gets called when the data context gets created. I haven't tested but I think you could also do:

YourDataContext dc = new YourDataContext();
dc.Connection.ConnectionString = "connection string here";

Here's an article that describes this as well - http://www.mha.dk/post/Setting-DataContext-Connection-String-at-runtime.aspx

Answer 2

How can we create the datacontext whith a different connection string?? Could you please more explain??

Answer 3

I managed to get around this issue in my application by executing the query using ADO.NET classes.

SqlCommand cmd = new SqlCommand("EXECUTE AS USER = 'operator'");
cmd.Connection = dc.Connection as SqlConnection;
cmd.Connection.Open();
cmd.ExecuteNonQuery();

// do the rest of the queries using linq to sql