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: 459
        
c#
|sql-server-2008-express
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