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)
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.
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.
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)