Create an SQL Express 2008 database in C# code, but login fails when trying to connect with a sysadmin

Question

I have a piece of code that creates an SQL Server Express 2008 in runtime, and then tries to connect to it to execute a database initialization script in Transact-SQL. The code that creates the database is the following:

private void CreateDatabase()
    {
        using (var connection = new SqlConnection(
            "Data Source=.\\sqlexpress;Initial Catalog=master;" +
            "Integrated Security=true;User Instance=True;"))
        {
            connection.Open();
            using (var command = connection.CreateCommand())
            {
                command.CommandText =
                "CREATE DATABASE " + m_databaseFilename +
                " ON PRIMARY (NAME=" + m_databaseFilename +
                ", FILENAME='" + this.m_basePath + m_databaseFilename + ".mdf')";
                command.ExecuteNonQuery();
            }
        }


    }

The database is created successfully. After that, I try to connect to the database to run the initialization script, by using the following code:

private void ExecuteQueryFromFile(string filename)
    {
        string queryContent = File.ReadAllText(m_filePath + filename);
        this.m_connectionString = string.Format(
            @"Server=.\SQLExpress; Integrated Security=true;Initial Catalog={0};", m_databaseFilename);
        using (var connection = new SqlConnection(m_connectionString))
        {
            connection.Open();
            using (var command = connection.CreateCommand())
            {
                command.CommandText = queryContent;
                command.CommandTimeout = 0;
                command.ExecuteNonQuery();
            }
        }
    }

However, the connection.Open() statement fails, throwing the following exception:

Cannot open database "TestData" requested by the login. The login failed. Login failed for user 'MYDOMAIN\myusername'.

I am completely puzzled by this error because the account I am trying to connect with has sysadmin privileges, which should allow me to connect any database (notice that I use a connection to the master database to create the database in the first place).

Answer 1

Is the user you are logging with have rights to the database 'TestData'?

If not grant the user the privileges required.

I am not sure if this means anything, but in your first create you are connecting to server

.\\sqlexpress

The second one is

.\SQLExpress

Answer 2

You'll need to issue a CREATE USER command (see: http://msdn.microsoft.com/en-us/library/ms173463.aspx) after creating the database but before trying to open a connction to that database.

For example:

CREATE USER 'MYDOMAIN\myusername' FOR LOGIN 'MYDOMAIN\myusername'

Answer 3

Is there a reason you specify User Instance=True when you create it but not when you try to connect to it?

When you create it after connecting with User Instance, it will create the database files but does not attach it to your actual instance. You'll either have to not specify User Instance=True in the first connection string or add it to the second and specify the database file to use.