Create SQL Contained Database Users using EntityFramework Code First

Introduction

As it is a normal scenario for each application to connect to a database to read/write data, there are some considerations we need to take care about, one of them is the account which will be used to connect to our database.

1. It is preferred that you start with an account with the least permissions and then continuously add more permissions if you need that (for example, if we start with a user account which has a role of db_owner so it has the ability to drop the database which may not be required by your application).
2. We can start with a user account that only has the ability to only read/write data from/to the database.
3. Our user will be contained database user (exist only on your database and doesn't have matching login in master database) so our database should be portable now and can be moved to any other server without depending on any other objects (like login in master database).

Code

So, based on that concept, I will assume that we are using Entityframework code first approach and we already have an initial migration script which is responsible for initializing our database (creating the database + creating initial structure of tables)

It should be something like that (notice that I'm using .NET core and entityframework core) containing 2 methods (Up and Down) which are responsible for creating and dropping our database.

public partial class Initial : Migration
{
   protected override void Up(MigrationBuilder migrationBuilder)
   {
      // your database initialization code should be here
   }

   protected override void Down(MigrationBuilder migrationBuilder)
   {
      // your database dropping code should be here
   }
}

Now, I will add a number of extension methods to MigrationBuilder class to be used to create our database user, they should be inside MigrationBuilderExtension class.

public static class MigrationBuilderExtension
{
     const string Password = "mypassword";
     // in this example, I will dynamically create username from database name,
     //  but in case you have fixed username, it should be like this 
     // const string UserName = "myusername";
     public static void CreateAppUser(this MigrationBuilder migrationBuilder)
     {
            StringBuilder sql = new StringBuilder();
            sql.Append("Declare @sqlstmt nvarchar(500);");
            sql.Append("Declare @user nvarchar(100);");
            sql.Append("Declare @pass nvarchar(100) = '"+ Password + "' + _
            Lower(REPLACE(DB_NAME(), '-', ''));");
            sql.Append("Select @user = Lower(REPLACE(DB_NAME(), '-', '_')) + '_app_user' ;");
            sql.Append("Set @sqlstmt='CREATE User '+@user +' _
            WITH PASSWORD ='''+@pass +'''';");
            sql.Append("Exec (@sqlstmt);");
            migrationBuilder.Sql(sql.ToString());
     }

     public static void DropAppUser(this MigrationBuilder migrationBuilder)  
     {
            StringBuilder sql = new StringBuilder();
            sql.Append("Declare @user nvarchar(100);");
            sql.Append("Select @user = Lower(REPLACE(DB_NAME(), '-', '_')) + '_app_user' ;");
            sql.Append("EXEC Drop User @user;");
            migrationBuilder.Sql(sql.ToString());
     }

     public static void AddAppUserToRole(this MigrationBuilder migrationBuilder, string roleName)
     {
            StringBuilder sql = new StringBuilder();
            sql.Append("Declare @role nvarchar(100) = '"+ roleName +"';");
            sql.Append("Declare @user nvarchar(100);");
            sql.Append("Select @user = Lower(REPLACE(DB_NAME(), '-', '_')) + '_app_user' ;");
            sql.Append("EXEC sp_addrolemember @role, @user;");
            migrationBuilder.Sql(sql.ToString());
     }

     public static void RemoveAppUserFromRole(this MigrationBuilder migrationBuilder, string roleName) 
     {
            StringBuilder sql = new StringBuilder();
            sql.Append("Declare @role nvarchar(100) = '" + roleName + "';");
            sql.Append("Declare @user nvarchar(100);");
            sql.Append("Select @user = Lower(REPLACE(DB_NAME(), '-', '_')) + '_app_user' ;");
            sql.Append("EXEC sp_droprolemember @role, @user;");
            migrationBuilder.Sql(sql.ToString());
     } 
}

And now, we can call our methods from inside Up/Down methods like this to create the user and add only read/write permissions (using built in roles db_datawriter and db_datareader).

protected override void Up(MigrationBuilder migrationBuilder)
{
   // your database initialization code should be here
   migrationbuilder.CreateAppUser();
   migrationbuilder.AddAppUserToRole("db_datareader");
   migrationbuilder.AddAppUserToRole("db_datawriter");   
}

protected override void Down(MigrationBuilder migrationBuilder)
{
  // your database dropping code should be here
   migrationbuilder.RemoveAppUserFromRole("db_datareader");
   migrationbuilder.RemoveAppUserFromRole("db_datawriter");
   migrationbuilder.dropappuser(); 
}

Now you need to update your connectionstring to use a new user account for connecting to the database.

Points of Interest

Use dynamic user name based on database name (as in my scenario, I have more than one instance of the database for different clients)

History

• Initial post