Microsoft sql server ошибка 2571

SQL server error 2571 triggers while trying to attach the MDF and LDF files. This error message indicates that the User does not have the permission to run DBCC checkprimaryfile to attach the database.

As a part of our Server Management Services, we help our Customers to fix SQL related errors regularly.

Let us today discuss the possible causes and fixes for this error.

What is SQL server error 2571?

Users often need to attach the databases either after a server upgrade or during a recovery. SQL server error 2571 triggers while trying to attach the MDF and LDF files

A typical error message looks like:

It generally happens due to insufficient permission of the user requesting the action. Let us now look into the steps to fix the issue.

How to fix SQL server error 2571?

As we discussed earlier , the error generally happens due to insufficient permission of the user to execute the task.

For instance, it happens when the SQL user login used is a part of the public role. Providing SYSADMIN rights to this account will help to solve it and bypass the error.

To Grant full control to the .MDF and .LDF files to user account, right click the files, select Properties, and modify the required fields in the Security tab.

However, it is not a good idea to give SYSADMIN rights to someone who just want to attach the database files. A better solution would be to use T-SQL to attach the database and provide dbcreator role to the login who is trying to attach the files.

CREATE DATABASE SQLAuthority
ON (FILENAME = 'F:DataSQLAuth.mdf'),
(FILENAME = 'F:LogSQLAuth_log.ldf')
FOR ATTACH;

Above command requires CREATE DATABASE, CREATE ANY DATABASE, or ALTER ANY DATABASE permission.

Apart from the above steps to change permission to attach the database, a best way is to copy the database. We could copy over the backup file and restore the database from it.

[Need any further assistance in fixing SQL errors? – We’re available 24*7]

Conclusion

In short, SQL server error 2571 triggers while trying to attach the MDF and LDF files. This error message indicates that the user does not have the permission to run DBCC checkprimaryfile to attach the database. Today, we saw how our Support Engineers fix this error.

var google_conversion_label = «owonCMyG5nEQ0aD71QM»;

Hi,
I am trying to connect to SQL Server 2005 via Excel/ODBC. (ODBC Test IS
successfull) and get the following error after entering Login ID and Passwor
d:
Connection failed:
SQLState: ‘37000’
SQL Server Error: 2571
….does not have permission to run DBCC TRACEON
Security setup on SQL Server seems to be fine (I can connect to SQL Server
from Reporting Services on same Login/Password).
Any idears?
Best regards,
SorenTo run DBCC Traceon you requires membership in the sysadmin fixed
server role.
Regards
Amish|||That did it! Thanks !!!
«amish» wrote:

> To run DBCC Traceon you requires membership in the sysadmin fixed
> server role.
>
> Regards
> Amish
>|||I had the same problem, but there are multiple users who use that
spreadsheet using Windows authentication. I did not want any of them to
be sysadmins. They only had SLECT permissions to certain tables. In my
case, deleting the Application name (from the SQL Server Login dialog)
fixed it. You need to click the Options button on the dialog to see
this. The application name seems to be an issue if it has certain
characters (like =AE in Microsoft=AE Query). Depending on the MS Office
version, the default value may be Microsoft=AE Query/MS Office XP/’.
You can also programmatically change the connection string (and even
the command text if necessary). Here is a sample.
‘ChangeConnection
Sub ChangeConnection()
Dim sh As Worksheet
Dim qt As QueryTable
Dim sConnection As String
For Each sh In ActiveWorkbook.Sheets
For Each qt In sh.QueryTables
‘Show current connectionstring
MsgBox («Tab: » & sh.Name & vbCr & » Current Connection: «
& vbCr & qt.Connection)
‘Show current query
MsgBox («Tab: » & sh.Name & vbCr & «Current Query: » & vbCr
& qt.CommandText)
‘Change Connection
qt.Connection =3D «ODBC;DRIVER=3DSQL
Server;SERVER=3Dmyserver;DATABASE=3DmyDB
;Trusted_Connection=3DYes;APP=3DExc=
el_TopCustomers;»
‘Change Qry text (the owner for instance)
qt.CommandText =3D Replace(qt.CommandText, «DB.dbo.»,
«DB.Me»)
qt.SavePassword =3D False
‘Show new connectionstring
MsgBox («Tab: » & sh.Name & vbCr & «Connection: » & vbCr &
qt.Connection)
‘Show new query
MsgBox («Tab: » & sh.Name & vbCr & «Query: » & vbCr &
qt.CommandText)
Next qt
Next sh
End Sub|||I have the exact same problem using Excel 97/MS-Query8. I tried your
suggestion, it did not work. I am still getting the error.
—
Ade
«preddy» wrote:

> I had the same problem, but there are multiple users who use that
> spreadsheet using Windows authentication. I did not want any of them to
> be sysadmins. They only had SLECT permissions to certain tables. In my
> case, deleting the Application name (from the SQL Server Login dialog)
> fixed it. You need to click the Options button on the dialog to see
> this. The application name seems to be an issue if it has certain
> characters (like ? in Microsoft? Query). Depending on the MS Office
> version, the default value may be Microsoft? Query/MS Office XP/’.
> You can also programmatically change the connection string (and even
> the command text if necessary). Here is a sample.
> ‘ChangeConnection
> Sub ChangeConnection()
> Dim sh As Worksheet
> Dim qt As QueryTable
> Dim sConnection As String
> For Each sh In ActiveWorkbook.Sheets
> For Each qt In sh.QueryTables
> ‘Show current connectionstring
> MsgBox («Tab: » & sh.Name & vbCr & » Current Connection: «
> & vbCr & qt.Connection)
> ‘Show current query
> MsgBox («Tab: » & sh.Name & vbCr & «Current Query: » & vbCr
> & qt.CommandText)
> ‘Change Connection
> qt.Connection = «ODBC;DRIVER=SQL
> Server;SERVER=myserver;DATABASE=myDB;Tru
sted_Connection=Yes;APP=Excel_TopC
ustomers;»
> ‘Change Qry text (the owner for instance)
> qt.CommandText = Replace(qt.CommandText, «DB.dbo.»,
> «DB.Me»)
> qt.SavePassword = False
> ‘Show new connectionstring
> MsgBox («Tab: » & sh.Name & vbCr & «Connection: » & vbCr &
> qt.Connection)
> ‘Show new query
> MsgBox («Tab: » & sh.Name & vbCr & «Query: » & vbCr &
> qt.CommandText)
> Next qt
> Next sh
> End Sub
>|||Sorry my mistake, it does work. You have to replace the string «Microsoft?
Query» with something else.
—
Ade
«Ade» wrote:
[vbcol=seagreen]
> I have the exact same problem using Excel 97/MS-Query8. I tried your
> suggestion, it did not work. I am still getting the error.
> —
> Ade
>
> «preddy» wrote:
>