Tham gia ngày: 11/7/16 Bài viết: 21 Đã được thích: 0 Điểm thành tích: 3 Giới tính: Nam
Khi bạn thao tác attach một database vào trong trình quản lý SQL manager studio thì hay xuất hiện lỗi: unable to open the physical file operating system error 5 5(access is denied.).
Nguyên nhân lỗi:
Thường là do quyền truy cập của bạn chưa đủ để được phép attach database.
Cách khắc phục:
Chạy SQL Manager Studio với quyền administrator thì bạn được toàn quyền thao tác với database.
I'm trying to execute the following script in SQL Server Management Studio: USE [master] GO CREATE DATABASE [test1] ON PRIMARY ( NAME = N'test1', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\test1.mdf', SIZE = 70656KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB) LOG ON ( NAME = N'test1_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\test1_log.ldf', SIZE = 164672KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
But I'm getting the error:
Msg 5123, Level 16, State 1, Line 2CREATE FILE encountered operating system error 5 (Access is denied.) while attempting to open or create the physical file 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\test1.mdf'.
Msg 1802, Level 16, State 4, Line 2 CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
Already have all role permissions for my user, any ideas on what's wrong?
Summary: The error “Unable to open the physical file” in SQL Server usually occurs due to lack of permissions to create the database. In this post, we will mention the solutions to fix this SQL Server error. In addition, we will mention a SQL repair software that can help recover database in case it gets corrupt or damaged.
Contents
When trying to create a database in SQL Server, you may encounter an error, saying “Unable to open the physical file.” This prevents you from creating the database.
The complete error message looks like:
Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file “R:\productions\stellar_database.mdf”. Operating system error 5: “5(Access is denied.)”.
Msg 1802, Level 16, State 7, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
The access denied error means that the user creating the database does not have permissions to create files in the folder.
Solutions to Fix the Error “Operating system error 5: “5(Access is denied.)” in SQL Server
You can try the following solutions to resolve this SQL Server error.
1. Check and Grant the Required Permissions
You need to first check that the user has the required permissions.
For this, go to Windows Explorer, right-click the production folder, and select Properties.
Verify if the user executing the T-SQL statement to create the database has permissions to the folder.
If not, press the Edit button. If the user is not in the list, press the Add button to add the user.
You can enter the object name. You can use the Check Name option to verify if you entered it correctly.
If you do not know the name, press the Advanced button to search for the user’s name.
Write your query with the start name or description. In Object Types, you can filter to look at users only. In Locations, you can select a computer or an Active Directory domain.
Once added, make sure to provide Modify permission.
Note: If you do not have permission to grant permissions for the folder, you can ask the system administrator to grant the required permissions.
2. Run as Administrator
Another common problem is that you do not have enough permissions because you are not running the SQL Server Management Studio (SSMS) as an administrator. In this case, the solution is very simple.
In the Windows menu, look for the SSMS, right-click on it, and select the option Run as administrator.
What to do if your database file is damaged or corrupted?
Your database file may get damaged or corrupted due to hardware problem, natural disaster, virus/malware attack, or any other problem. In such a case, you can restore the database from the backup.
If you do not have a current backup, then you can install specialized software to repair the corrupt database. One such SQL repair software is Stellar Repair for MS SQL. This software is created exclusively to fix corrupt or damaged SQL databases.
Using the software is also simple. You just need to provide the name of the database file(s) and the software will fix it for you.
Let’s see how it works.
Before proceeding, bring the database offline. Once it is offline, create a copy and work with the copy. This way you will have a backup, in case something goes wrong.
Now, launch the software and click Browse to select the database file.
Optionally, you can use the Find button to find the database file.
After selecting the database file, press the Repair button to repair your database.
Once repaired, you can save the data. Press the Save icon. You can create a new database or replace the existing one.
In addition, you can save the tables’ data and views in other files, like Excel, CSV, and HTML.
If everything goes fine, you will be able to recover your data.
Conclusion
In this post, we learned how to fix the error “Operating system error 5: “5(Access is denied.)” in SQL Server. The main reason for this error is the lack of permissions. In this case, you need an administrator or a user with privileges to provide permissions to modify files in the folder. In addition, we learned how to fix a damaged database using Stellar Repair for MS SQL.
About The Author
Daniel Calbimonte
Daniel Calbimonte is a Microsoft Most Valuable Professional, Microsoft Certified Trainer, and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at IT Academies and has over 10 years of experience as a QE and developer for SQL Server related software. He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs. Read more