SQL SERVER – Database Attach Failure – Msg 2571 – User ‘guest’ Does Not Have Permission to Run DBCC Checkprimaryfile.

One of my clients was trying to recover from a disaster and wanted to attach the MDF and LDF files. While trying to do that, they encountered an error. In this blog we would learn about how to fix the error – User ‘guest’ does not have permission to run DBCC checkprimaryfile while attaching the database.

Here is the exact error message which was encountered while attaching the database.

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
User ‘guest’ does not have permission to run DBCC checkprimaryfile. (Microsoft SQL Server, Error: 2571)

Here is the UI with the error message.

SQL SERVER - Database Attach Failure - Msg 2571 - User 'guest' Does Not Have Permission to Run DBCC Checkprimaryfile. checkprimaryfile-err-01-800x724

To look further, we captured profile to find exact command which is failing. We found following command in profiler before failure.

DECLARE @Path NVARCHAR(255)
DECLARE @Name NVARCHAR(255)
DECLARE @fileName NVARCHAR(255)
SELECT @fileName = N'F:\Data\SQLAuthority.mdf'
DECLARE @command NVARCHAR(300)
SELECT @command = 'dbcc checkprimaryfile (N'''+ @fileName + ''' , 2)'
CREATE TABLE #smoPrimaryFileProp(property SQL_VARIANT NULL, value SQL_VARIANT NULL) 
INSERT #smoPrimaryFileProp 
EXEC (@command)
SELECT
p.value AS [Value]
FROM
#smoPrimaryFileProp p
DROP TABLE #smoPrimaryFileProp

Above also fails with the same error in SSMS when we use the same login which is trying to attach the database.

SQL SERVER - Database Attach Failure - Msg 2571 - User 'guest' Does Not Have Permission to Run DBCC Checkprimaryfile. checkprimaryfile-err-02

WORKAROUND/SOLUTION

After looking at properties of the login, I found that current user was part of the public role and it was a SQL Login. The only solution which I could find was to give SYSADMIN rights to this account to use UI and bypass the error. I don’t believe that it’s a good idea to give SYSADMIN to someone who just wants to attach the database files. I feel 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:\Data\SQLAuthority.mdf'),   
   (FILENAME = 'F:\Log\SQLAuthority_log.ldf')   
FOR ATTACH;

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

I have written a blog decade ago to find all possible DBCC commands. You can read it using below link.  SQL SERVER – DBCC commands List – documented and undocumented

Reference: Pinal Dave (https://blog.sqlauthority.com)

, , , , ,
Previous Post
SQL SERVER – Fix Error 3271: A nonrecoverable I/O error occurred on file. The remote server returned an error: (404) Not Found
Next Post
What is the ROI of a SQL Server Monitoring Tool?

Related Posts

Leave a Reply

Menu