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

Posted by Andrés Gonzales on Stack Overflow See other posts from Stack Overflow or by Andrés Gonzales
Published on 2010-05-13T12:58:28Z Indexed on 2010/05/13 13:04 UTC
Read the original article Hit count: 312

Filed under:
|

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).

© Stack Overflow or respective owner

Related posts about c#

Related posts about sql-server-2008-express