c# – Problem while creating SQL Server database from C # (file permissions)

Question:

The situation is this, I create a base from a C # application with the following code

CreateDB_SQL_command = "CREATE DATABASE " + "ExpData" + " ON PRIMARY " +
      "(NAME = Exp_Data_" + @DbName + ", " +
      @"FILENAME = '" + @DbFileName + "', " +
      "SIZE = 100MB, MAXSIZE = 10GB, FILEGROWTH = 10%) " +
      "LOG ON (NAME = " + @DbLogName + ", " +
      @"FILENAME = '" + @DbLogFileName + "', " +
      "SIZE = 100MB, " +
      "MAXSIZE = 5GB, " +
      "FILEGROWTH = 15%)";

  SqlCommand myCommand = new SqlCommand(CreateDB_SQL_command, myConn);
  try
  {
    myConn.Open();
    myCommand.ExecuteNonQuery();
  }

The base is created, everything is fine, it is perfectly managed from ManagmenStudio, but when you try to open it

switch (open_db_file.ShowDialog())
  {
    case System.Windows.Forms.DialogResult.OK:
      if (open_db_file.FileNames.Count() > 1)
      {
        MessageBox.Show("необходимо выбрать только 1 файл", "ошибка");
        return;
      }
      FilePath = open_db_file.FileName;
      break;
    case System.Windows.Forms.DialogResult.Cancel:
      return;
  }

  str_build = new SqlConnectionStringBuilder();
  str_build.DataSource = ".\\SQLEXPRESS";
  str_build.IntegratedSecurity = true;
  str_build.InitialCatalog = FilePath;
  str_build.MultipleActiveResultSets = true;


  using (ExpDataContext _db = new ExpDataContext(str_build.ToString()))
  {
    //.......
  }

When I select a file in the OpenFileDialog I get a MessageBox which says that I do not have permission to access this file. The problem can be cured if you manually assign permissions to this file from the current windows user through the explorer. Actually the question is, is it possible to somehow configure SQL Server or change the script for creating a database so that the current user, or at least everyone, has the right to access the .mdf file? I read the manual from msdn, but did not find the answer there.

Answer:

Why do you need access to the database file at all (through the explorer)? The application works with the database through SQL Server, all it needs to know is the name of the database – InitialCatalog . Which may not be the same as the file name!

Enter InitialCatalog = "master". Open the connection and execute the request

SELECT name
FROM sys.databases
WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb');

The result – a list of all non-system databases – load it into the combobox and show it to the user. Substitute the user-selected value as InitialCatalog in your code instead of FilePath .

Scroll to Top