LinqToSql stored procedures and executing as a different user? RRS feed

  • Question

  • Hi,

    We use integrated security for our DataContext connections and also call stored procedures on SQL Server 2008.

    We are moving to a tighter security model where we have sql users that only have access to certain tables on the databases.  This means that the integrated security simply connects to the server and then we want to implement Users and Roles for security.  How do you do this with Linq To Sql?

    If we were using T-SQL this would be accomplished via the "EXECUTE AS()" function"

    EXECUTE (SELECT * FROM FOO) AS user_ReportViewer

    How can I accomplish the same type of security model when dealing with stored procedures and LinqToSql?

    Thanks in advance!

    Wednesday, March 25, 2009 11:39 PM


  • Hi Matt,
    Typically, when using Linq to SQL, security is not architected at the DB level but at a service layer so your functions would be of the form:

    public void DoSomethingAtTheDB()
        Assert(... user is in some role ....);

        perform Linq functions



    However, if you do have to have security at the sql user level, you could have different DataContext clases where you specify the sql user/password in the Connection strings and use the appropriate DataContext like so:

    public void DoSomethingAtTheDB()
       perform Linq functions using ReportViewerDataContext;

    public void DoSomethingAtTheDB2()
       perform Linq functions using ReportEditorDataContext;

    Tuesday, March 31, 2009 4:35 AM