I'm creating a database that has two main parts: a front end which the customer sees and a backend that only the company can see.

It is an email subscriber system so I want the customer to be able to view, add, edit and delete their entries in the email subscriber table, but all the admin-related tables should be hidden from them.

As I'm fairly new to SQL Server 2017, I'm still getting my head around logins, users and roles.

The main question I wanted to ask is what is the best way of doing this? I know I can manually set up database users and give them grant/deny permissions, but how do I do this automatically so that every time a customer's details are added to a new row in the customer table, a new database user in the sidebar is added.

1 Answers

0
Onur Gelmez On

Try this

public static void AddUsersToDatabase(string databaseserver, string databasename, string usertobeadded)
{
    using (SqlConnection conn = new SqlConnection("server=" + databaseserver + "; database=" + databasename + "; User ID=WPDOMAIN\\spdev; Integrated Security=SSPI;  password=Password123;"))
    {
        conn.Open();

        string password = "Password123";

        string sql = "CREATE LOGIN " + usertobeadded + " WITH PASSWORD = '" +
            password + "';  USE " + databasename + "; CREATE USER " + usertobeadded + " FOR LOGIN " + usertobeadded + ";";

        SqlCommand cmd = new SqlCommand(sql);
        cmd.ExecuteNonQuery();


        conn.Close();
    }
}