CODEWITHSUNDEEP
sqlsql-server
max
max

Reputation: 10444

sql permission issue when creating a database

I've made a c# program to create a database in the localhost ms sql as below:

    SqlConnection conn = new SqlConnection();
    conn.ConnectionString = "SERVER = " + serverName + "; DATABASE = master; Integrated Security=SSPI";

    string query = " CREATE DATABASE " + dbName + " ON PRIMARY "
                        + " (NAME = " + dataFileName + ", "
                        + " FILENAME = '" + schemaPath + "', "
                        + " SIZE = 2MB,"
                        + " FILEGROWTH = 10%) "
                        + " LOG ON (NAME =" + logFileName + ", "
                        + " FILENAME = '" + logFilePath + "', "
                        + " SIZE = 1MB, "
                        + " FILEGROWTH = 10%) ";

    try
    {
        conn.Open();
        SqlCommand sqlCmd = new SqlCommand(query, conn);
        sqlCmd.ExecuteNonQuery();
        Console.WriteLine("Database has been created successfully!");
    }
    catch (System.Exception ex)
    {
        Console.WriteLine("Failed to create the db. " + ex.ToString());
        Environment.Exit(1);
    }

It works perfectly fine on my pc, but fails on another machine: I do have the write permissions to the folders which I've specified.

Failed to create the db. System.Data.SqlClient.SqlException (0x80131904): Directory lookup for the file "C:\Users\Administrator\Desktop\test2.mdf" failed with the operating system error 5(Access is denied.). CREATE DATABASE failed. Some file names listed could not be created. Check related errors. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, Tds ParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName,Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at com.appneta.test.DB_DataLoader.createDB(String dbName)

Any ideas?

Upvotes: 1

Views: 1654

Answers (2)

Sico
Sico

Reputation: 1183

Are you running visual studio as Administrator?

Upvotes: 0

Mike Walsh
Mike Walsh

Reputation: 899

When you say "I do have the write permissions to the folders which I've specified." - it feels like you are talking about you the user.

You need to make sure that the service account SQL Server is running under has write permissions on the folder path you are trying to write to.

Upvotes: 2

Related Questions