I had a terribly stressful evening at work last night. I brought a database offline to move a file and when I tried to bring the database back online I received the following error:
Msg 5011, Level 14, State 7, Line 1
User does not have permission to alter database ‘DatabaseName’, the database does not exist, or the database is not in a state that allows access checks.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Nothing I tried worked and a Google search was not helpful. Here is what happened:
I made a mistake right from the beginning, I should have moved the file before I set the database offline, but that is not what caused the problem:
ALTER DATABASE DatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE DatabaseName SET OFFLINE;
— copy the file to the new location…
ALTER DATABASE DatabaseName MODIFY FILE
(
Name = FileName,
Filename = ‘F:\New\Location\Name.mdf’
);
ALTER DATABASE DatabaseName SET ONLINE;
ALTER DATABASE DatabaseName SET MULTI_USER;
— delete old file…
As soon as I tried to set the database online, I received the error message: User does not have permission to alter database ‘DatabaseName’, the database does not exist, or the database is not in a state that allows access checks.
I did not think this was a permissions problem. At first I chased the part of the error that the database is not in a state that allows access checks. This led me nowhere because it makes sense that the database was not in a state that allows access checks because it has been set offline.
So a coworker looked at the file I moved. He discovered that file did not inherit permissions from the parent folder and the permissions were wrong. As soon as I reset permissions on the file I was able to set the database back online.
This was a frustrating problem, but I learned something I can add to my toolbox. Here is what I will do in the future:
ALTER DATABASE DatabaseName MODIFY FILE
(
Name = FileName,
Filename = ‘F:\New\Location\Name.mdf’
);
ALTER DATABASE DatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE DatabaseName SET OFFLINE;
— copy the file to the new location…
— reset permissions on the file…
ALTER DATABASE DatabaseName SET ONLINE;
ALTER DATABASE DatabaseName SET MULTI_USER;
— delete old file…
