locked
WITH EXECUTE AS 'SqlUser1' EF Code First [Update-Database -Verbose] RRS feed

  • Question

  • User1080785583 posted

    I am trying to use the Entity Framework 6.1 Code First approach with generating my sql stored procedure. The issue is I need to supply the service account which to allow permissions to execute. Currently it is generating the procedure under my current AD account. Curious how I can implement this with the command CreateStoredProcedure.

    I need to be able to execute a nuget package manager command called, "Update-Database -Verbose", that will generate the required SQL statement to create the database object. Below is what I need to do.

    public partial class CreateStoredProcedureDelete : DbMigration
        {
            public override void Up()
            {
                CreateStoredProcedure("Delete", p => new { id= p.Int() },
                    @"WITH EXECUTE AS 'SqlUser1'  
                        AS 
                     delete from XXX
            }
            
            public override void Down()
            {
                DropStoredProcedure("Delete");
            }
        }

    Executing Update-Database -Verbose generates the following T-SQL 


    CREATE PROCEDURE [Delete] @Id[int] AS BEGIN WITH EXECUTE AS 'SqlUser1' AS delete from XXX -- etc END

    Then I receive an error from aforementioned code: Incorrect syntax near the keyword 'EXECUTE'.

    Thanks.

    Friday, December 21, 2018 5:34 PM

Answers

  • User475983607 posted

    I generally use the Sql() method to the write SQL scripts. 

    public partial class CreateStoredProcedureDelete : DbMigration
    {
    	public override void Up()
    	{
    		CreateStoredProcedure("ProcName", p => new { id= p.Int() },
    			@"the proc body")
    			
    		Sql(@"GRANT EXECUTE ON dbo.ProcName TO theAccount")
    	}

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, December 21, 2018 5:56 PM