Click here to Skip to main content
15,861,172 members
Articles / Programming Languages / SQL
Tip/Trick

Create SQL Contained Database Users using EntityFramework Code First

Rate me:
Please Sign up or sign in to vote.
4.06/5 (5 votes)
19 Aug 2018CPOL2 min read 8.6K   9   1
How to create contained DB users to be used among your application

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.

C#
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.

C#
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).

C#
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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior) Emaratech
United Arab Emirates United Arab Emirates
I'm Senior Full Stack Web Developer (MCTS, PSM 1)
with more than 8 years of experience in software development specially using .Net Technologies
i also have experience developing SPA applications using popular frameworks and tools like AngularJS, Angular, Vue.js and typescript

Comments and Discussions

 
QuestionHave you consider to post this as a tip? Pin
Nelek20-Aug-18 0:39
protectorNelek20-Aug-18 0:39 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.