Resolve Microsoft SQL Database Access Denied Error Code 5123
Talking about data management, most of the time, organizations only prefer using Microsoft SQL Server. This server is the most powerful and efficient database management system. In just a few clicks, users can manage tons of data using this application. But, sometimes, users face SQL server database access denied error. In this blog, we attempt to know about a specific SQL server error. Its occurrence, reasons, and solutions will be mentioned. We are going to discuss “SQL database access denied” error today. The error has a code 5123 and is also called as “SQL Server Error 5123”. This error looks something like this:
Reasons for the Error
The SQL error mentioned above tells us that the SQL database file opening has failed or access to open/create this particular file has been denied. Let’s see why this would have happened.
- Removing SQL database files (MDF and NDF) from their default location on the system drive.
- Permission to access the particular SQL server database file has not been granted to the administrator.
Both the reasons mentioned above are highly probable for the “SQL Database Access Denied” error. Let’s move forward to learn the solutions for resolving this issue.
Solutions for Resolving SQL Database Access Denied Error 5123
There are two way to solve SQL Database error: Manual and Automated solution:
Manual Workarounds to Solve SQL Server Error Code 5123
There are four manual solutions to fix SQL database error code 5123:
Method 1 – Grant Permission
The following are the steps to resolve Microsoft SQL Server error 5123:
- Right-click on the .mdf file you want to attach
- Select “Properties” to see if permission has been granted
- Hit “Add” to grant login permission. Provide full control while logging in.
Note: This can also be performed for corresponding LDF file. A user can also attempt to “Run as Administrator” rather granting permission to everyone.
Method 2 – Using SQL Service Management Studio (SSMS)
- Using a valid domain name and Windows Authentication, log in to SSMS
- Choose the “Object Explorer” option and click on “Databases” to view the existing database list
- Right-click “Database”. A drop-down menu will appear, click “Attach”
- After a new “Attach Database” window appears, click on “Add”
- Now, choose the database you want to attach
- Once the MDF and LDF files are verified, click “OK”.
All the attached database of SQL can be seen here.
Method 3 – Using T-SQL (Transaction SQL)
Database can easily be attached in Microsoft SQL Server by users with the help of sp_attach_db stored procedure. Let us see how:
sp_attach_db [ @dbname= ] 'dbname', [ @filename1= ] 'filename_n' [,...16 ]
[ @dbname= ] IS the name of database that can be attached to a SERVER.
[ @filename1= ] 'filename_n' Is the physical name, which includes location of a database file.
Method 4 – Using sp_attach_db stored procedure (deprecated)
sp_attach_db
[@dbname=] ‘dbname’ [@filename1=] ‘filename_n’ [ ,…16 ]
[@dbname=] – Refers to the name of the database to be attached to the server.
[@filename1=] – ‘filename_n’ Is the physical name, including path, of a database file
//SAMPLE: EXEC sp_attach_db
@dbname = N’MyDatabase’,
@filename1 = N’D:\DataFiles\MyDatabase_Data.mdf’,
@filename2 = N’E:\LogFiles\MyDatabase_Log.ldf’;
Automated Solution to Resolve the SQL Database Access Denied Error
If the above stated methods do not work to fix SQL Server error, users can try using third-party software. We have come with amazing software which will do the work for users in an efficient manner and without wasting a lot of time. FreeViewer SQL Recovery Tool helps in recovering corrupt .mdf and .ndf SQL server database file.
The software can recover deleted records from database tables without losing even a single bit of data. It lets you recover multiple NDF database files and export them into SQL Server Database directly.
Features of SQL Recovery Tool
- The software is capable of scanning and recovering Triggers, Rules, Functions, Tables, and Stored-Procedures.
- It exports file into SQL Server Database or as SQL Server Compatible Scripts.
- This tool has an option to auto fetch SQL Server Name while Exporting to SQL Server Database.
- It previews Deleted Table, Stored Procedure, Function, Triggers, Rules, Indexes, etc in Red Color.
- The tool supports ASCII and Unicode XML datatype.
Let’s find out how this tool works. Follow the steps to recover SQL database records.
1. Launch the software and click on “Open”. Browse for your desired MDF file and open it
2. Choose “Quick Scan” option. Mark the “Auto Detect” option
3. After scan completed, choose “SQL Server Database” option and enter SQL “Server Name”
Note: If the server name you entered doesn’t show up, you can choose Windows Authentication and enter the credentials to login to SQL Server.
4. Choose “Export to existing Database” and choose the MDF file to be exported “With Only Schema” option and hit “Export”
5. When the export completes, user will be prompted to save .csv file, hit “Yes” and browse for location to save resultant file and click “OK”.
After scanning process, “Quick Scan Mode” also prompts users to use “Advance Scan Mode” of the expected error has not been found. You can choose to export files as compatible scripts by choose the “Export as SQL Server Compatible Script” option.
In case you want to export your recovered data into a new database, you can do so by choosing the “Create a new Database” option. You can also choose to save all the components along with data by choosing the “With Schema and Data”.
Last Words
In the above reading, we learnt the reasons for the error “SQL Database Access Denied” and the manual solutions to resolve the error. Manual solutions are useful but if they do not work, you can use the automated solution mentioned above. The SQL Recovery Tool is your best shot at recovering you SQL MDF files. It can restore SQL data files in just a few clicks and is self-explanatory. The software recovers SQL Server 2019, 2017, 2016, 2014, 2012, 2008 / 2008 R2, 2005, 2000 Database Files i.e. MDF / NDF. Also, .mdf files that have been affected by Wallet Ransomware can be recovered.
