0

I have a running Windows application that is using a SQL Server database called LakDB to run. I want to give an option to select another database file. The first thing I want to do is take the current database offline so I can setup the new data file with the same database name. But I keep getting this error:

System.Data.SqlClient.SqlException: User does not have permission to alter database 'LakDB', the database does not exist, or the database is not in a state that allows access checks. ALTER DATABASE statement failed.

The code I used is:

string conn = @"Server=(localdb)\MSSQLLocalDB;Integrated security=True;Connection Timeout=30";

using (SqlConnection c = new SqlConnection(conn))
{
    string query = "use master";

    using (SqlCommand cmd = new SqlCommand(query, c))
    {
        c.Open();
        cmd.ExecuteNonQuery();
        c.Close();
    }

    query = "alter database LakDB set offline with rollback immediate";

    using (SqlCommand cmd = new SqlCommand(query, c))
    {
        c.Open();
        cmd.ExecuteNonQuery();
        c.Close();
    }

    query = "drop database if exists LakDB";

    using (SqlCommand cmd = new SqlCommand(query, c))
    {
        c.Open();
        cmd.ExecuteNonQuery();
        c.Close();
    }
}

And I keep getting the error at the first ExecuteNonQuery command under the alter database LakDB query.

What else can I do?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • Does the database exist? – David Browne - Microsoft Feb 24 '23 at 19:43
  • Yes, because I'm running it in the program and I'm able to access it through that server, in the program. –  Feb 24 '23 at 19:58
  • 1
    https://stackoverflow.com/questions/9798968/permissions-required-to-run-alter-database-set-single-user-statement-on-sql-se. You may want to grant ALTER database permissions to your user. – Asdfg Feb 24 '23 at 20:04
  • Can you run query inside SQL Server Management Studio? First get running in SSMS which has better error messages to help solve issues. Not sure why you want to use Master. – jdweng Feb 24 '23 at 20:43
  • If you're not a sysadmin, and aren't the owner of the database, and lack the VIEW ANY DATABASE permission, you will not be able to discover that the database exists. But I would assume that you're an admin; but I suppose LocalDb might be wierd. – David Browne - Microsoft Feb 24 '23 at 21:42

0 Answers0