How to programmatically impose security on a programmatically created MS SQL database in C#

Question

I'm a complete beginner in setting up databases dynamically. I found this code which is used to create a database:

String str;
SqlConnection myConn = new SqlConnection ("Server=localhost;Integrated security=SSPI;database=master");
    
    str = "CREATE DATABASE MyDatabase ON PRIMARY " +
     "(NAME = MyDatabase_Data, " +
     "FILENAME = 'C:\\MyDatabaseData.mdf', " +
     "SIZE = 2MB, MAXSIZE = 10MB, FILEGROWTH = 10%)" +
     "LOG ON (NAME = MyDatabase_Log, " +
     "FILENAME = 'C:\\MyDatabaseLog.ldf', " +
     "SIZE = 1MB, " +
     "MAXSIZE = 5MB, " +
     "FILEGROWTH = 10%)";
    
    SqlCommand myCommand = new SqlCommand(str, myConn);
    try
    {
        myConn.Open();
        myCommand.ExecuteNonQuery();
        MessageBox.Show("DataBase is Created Successfully", "MyProgram", MessageBoxButtons.OK, MessageBoxIcon.Information);
    }
    catch (System.Exception ex)
    {
        MessageBox.Show(ex.ToString(), "MyProgram", MessageBoxButtons.OK, MessageBoxIcon.Information);
    }
    finally
    {
        if (myConn.State == ConnectionState.Open)
        {
            myConn.Close();
        }
    }

My problem is that how should I prevent other applications from accessing the created database without specifying credentials like a username and password? I want to be able to do this programmatically (without using MS SQL Server Studio) since it is deployed along with the C# application on the customer's PC.

Answer 1

I have tried this for fun a long time ago, from MSDN. Give it a try:

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();
    }
}