How to create a Database user in Entity Framework

asp.net-mvc entity-framework entity-framework-6 sql-server

Question

My first Entity Framework project has been in progress. I'll be producing a variety of SSRS reports as part of the project. I must have a Reports user with restricted access to the server's particular database in order to connect to it. I've usually added database users using scripts, but I'm wondering whether there's a method to do it instead using Entity Framework.

1
2
9/14/2017 9:20:24 PM

Accepted Answer

ZZZ_tmp
1
5/23/2017 12:33:53 PM

Popular Answer

If your user already has a login set up in SQL Server (Security > Logins), you may add the user to the database by using the following method from your DB initializer seed method:

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 precise SQL required could change. You may open the create user window in SSMS (Database > Users > New User...), complete the inputs, and click the "Script" button at the top rather than OK at the bottom to generate the SQL for your scenario. Before putting the resulting script into the procedure above, keep in mind that any "GO" lines must be removed.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow