Read-only solution when attaching an SQL Server database

Source: Internet
Author: User
Tags management studio sql server management sql server management studio connectionstrings

Symptom:
   When running the. NET program, the following message is displayed:"The Database "C:/program files/Microsoft ASP. NET/ASP. NET Ajax sample applications/v1.0.61025/contacts/app_data/contacts. MDF" cannot be updated because the database is read-only."
   
Environment:
 

  • Web. config Configuration:

   <Connectionstrings>
    <! -- Integrated Security = true; user instance = true -->
    <Add name = "contactsconnectionstring"Connectionstring =" Data Source =.; attachdbfilename = | datadirectory |/contacts. MDF; uid = sa; Pwd = 123456; "providername =" system. Data. sqlclient "/>
   </Connectionstrings>
 

  • Run SQL Server Management Studio (Start, Run: sqlwb ):

   The configuration file has the highest permissions and should not affect read/write. However, from the Database List, we found that the attached database is in the "read-only" status. This should be the reason why data cannot be modified.

Cause:

 

  • When a database is attached, the database file is read-only.
  • The account running the SQL server instance does not have the modification permission for the attached database files.

Solution:

 

  • Cancels the read-only attribute of a file.

   After canceling the "read-only" attribute of a file, you may still need to perform the following operations to view the database attribute.
 

  • View the account that runs the SQL server instance.

   On the services. msc console or the SQL Server Configuration Manager, View:
 


 
  • As shown in, if the account used to start the SQL server instance is network service, change it to local service and restart the SQL server instance. If the specified account is used to start the SQL server instance, perform the following operations.
  • Modify file security attributes.

   In this example, add the account sqlboot running the SQL server instance to the access permission list for the physical file contacts. MDF. If a log file exists, add the Log File Attribute accordingly. In fact, here we find that the account on which the SQL server instance is started can not be modified, directly add the account network service or local service that starts the SQL server instance to the access permission list for the database physical files.
   
Appendix:

 

  •  Attachment database T-SQL statement:

   Create Database contacts
   On (filename = 'C:/program files/Microsoft ASP. NET/ASP. NET Ajax sample applications/v1.0.61025/contacts/app_data/contacts. MDF ')
   For attach

  • Microsoft does not recommend that you use "sp_attach_db" to attach a database ." Important: this function will be deleted in later versions of Microsoft SQL Server. Avoid using this function in new development work, and modify the application that is currently using this function. We recommend that you use create database database_name for attach. "
Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.