How to create a Database user in Entity Framework

Question

Been working on my first Entity Framework project. As part of the project I am going to be creating a number of SSRS reports. In order to connect to the database I need to have a Reports user that will only access to the specific database on the server. In the past i have always written a script to add Database users but I want to know is there a way that i can do this using Entity Framework instead?

Answer 1

Assuming your user already has a login defined at the SQL Server level (Security > Logins), you can call the following method from your DB initializer seed method to add the user to the database:

private void AddDbUser(MyDataContext myDB)
{
    string accountDomainName = "AccountDomainName";  // replace with user's login domain
    string accountLoginID = "AccountLoginID";  // replace with user's login ID

    string sql = 
        "USE [MyDB]" +
        "CREATE USER [MyNewUser] FOR LOGIN [" + accountDomainName + "\\" + accountLoginID + "]" +
        "ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO [" + accountLoginID + "]" +
        "ALTER AUTHORIZATION ON SCHEMA::[db_datawriter] TO [" + accountLoginID + "]" +
        "EXEC sp_addrolemember N'db_datawriter', N'" + accountLoginID + "'" +
        "EXEC sp_addrolemember N'db_datareader', N'" + accountLoginID + "'";

    myDB.Database.ExecuteSqlCommand(sql);
}

The exact SQL needed is dependent on your configuration. To have SQL Server generate the SQL for your scenario, you could open the add user dialog in SSMS (Database > Users > New User...), fill out the fields, and click the "Script" button at the top instead of hitting OK at the bottom. Note that any "GO" lines will need to be removed from the generated script before pasting it into the method above.