As a SQL Server database administrator, you may encounter various errors while working with databases. One common error that many DBAs face is the “Access is denied” error when trying to create a file in the operating system. In this blog post, we will explore the causes of this error and discuss possible solutions.

The error message typically looks like this:

CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file 'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\DetachTest.mdf'. (Microsoft SQL Server, Error: 5123)

The key piece of information in this error message is “operating system error 5(Access is denied.)”. This indicates that the error is due to a permission issue with the file.

When a database is detached, the permissions of the database files are modified. If a SQL Login detaches the database, the permissions are given to the SQL Server Service account. If a Windows account detaches the database, the permission is given to the account that detached it.

To understand the real cause of the error, let’s reproduce it. We will create a database called DetachTest and set a SQL Service account as CONTOSO\SQLService. Then, we will log into the server using SSMS with the login CONTOSO\syadmin and detach the database. After detaching, we can view the permissions by going to the file > properties and security tab.

Now, let’s log in as the “sa” account, which is a SQL Login. As mentioned earlier, SQL Server will try to use a service account to read the file. However, the file is not readable by any account other than CONTOSO\sysadmin.

So, how can we avoid the “Access is denied” error? Here are the steps:

1. Right-click on the file mentioned in the error message and go to properties.
2. In the Properties dialog box, go to the Security tab.
3. In the Select Group or user names box (which has sysadmin in the above image), click the Edit button and enter the name you used to log in to SQL Server. If it’s a Windows account, use that account; otherwise, use the service account.
4. Click Check Names and verify that the name you are specifying exists as a SQL Server login.
5. Click OK.
6. In the Group or User Names box, select the user name you selected in the previous step and give full control permission to the user.
7. Click OK.

Now you are all set to attach database files using either SQL Server authentication or Windows authentication without encountering the “Access is denied” error.

Understanding SQL Server permissions and resolving access denied errors is crucial for smooth database operations. By following the steps outlined in this blog post, you can ensure that the necessary permissions are granted to the appropriate accounts, avoiding any permission-related issues.