DB attaches as read only

Hello all,
I detached one of our DB's from one of our servers, copied the data & log files to another server, and then attached it there. It
attaches as Read Only.
When I go into the DB's properties and try to change the Read-only option, I get an error message:
Error message box title bar: Microsoft SQL-DMO (ODBC SQLState: 42000)
Error 5105: Device activation error. The physical file name '(Data file's drive, path, & file name)' may be incorrect.
Device activation error. The physical file name '(Log file's drive, path, & file name)' may be incorrect.
Could not restart database '(DB's name)'. Reverting back to old status.
ALTER DATABASE statement failed.
Device activation error. The physical file name '(Log file's drive, path, & file name)' may be incorrect.
sp_dboption command failed.
What am I doing wrong? How do I successfully detach from one & reattach to another?
--
Thanks for any help anyone can provide,
Conan KellyConan Kelly wrote:
> Hello all,
> I detached one of our DB's from one of our servers, copied the data & log files to another server, and then attached it there. It
> attaches as Read Only.
> When I go into the DB's properties and try to change the Read-only option, I get an error message:
> Error message box title bar: Microsoft SQL-DMO (ODBC SQLState: 42000)
> Error 5105: Device activation error. The physical file name '(Data file's drive, path, & file name)' may be incorrect.
> Device activation error. The physical file name '(Log file's drive, path, & file name)' may be incorrect.
> Could not restart database '(DB's name)'. Reverting back to old status.
> ALTER DATABASE statement failed.
> Device activation error. The physical file name '(Log file's drive, path, & file name)' may be incorrect.
> sp_dboption command failed.
> What am I doing wrong? How do I successfully detach from one & reattach to another?
>
Check the service account that SQL Server is running under, make sure
that account has read/write permissions on the folder that those
attached files are in.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Kelly,
Check what permissions are set for users on the folder where the
database files are placed. If the permissions are inadequate, then
grant the necessary permissions to the user account you logged in with.
Having done that you can proceed to attach the DB files again.|||Tracy,
Thank you for the help.
It does look like permissions is the issue. When we moved the log/data files to SQL Servers default location (where tempdb, master,
model, Northwind, and pubs live), we can attach it just fine from there.
On further inspection, it appears that the SQL Server services are not running under its own (or even another's) domain user
account. It looks like it is running under the Local System account.
What are all the pro's & con's for using its own Domain User account vs using the Local System account?
I remember in my SQL Server Admin class, our instructor had us set up its own Domain User account and use that. I don't
think/remember if he explained why or if the book explains why, though.
Thanks again for all of your help,
Conan
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message news:454A4A41.6030007@.realsqlguy.com...
> Conan Kelly wrote:
>> Hello all,
>> I detached one of our DB's from one of our servers, copied the data & log files to another server, and then attached it there.
>> It attaches as Read Only.
>> When I go into the DB's properties and try to change the Read-only option, I get an error message:
>> Error message box title bar: Microsoft SQL-DMO (ODBC SQLState: 42000)
>> Error 5105: Device activation error. The physical file name '(Data file's drive, path, & file name)' may be incorrect.
>> Device activation error. The physical file name '(Log file's drive, path, & file name)' may be incorrect.
>> Could not restart database '(DB's name)'. Reverting back to old status.
>> ALTER DATABASE statement failed.
>> Device activation error. The physical file name '(Log file's drive, path, & file name)' may be incorrect.
>> sp_dboption command failed.
>> What am I doing wrong? How do I successfully detach from one & reattach to another?
> Check the service account that SQL Server is running under, make sure that account has read/write permissions on the folder that
> those attached files are in.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Conan Kelly wrote:
> Tracy,
> Thank you for the help.
> It does look like permissions is the issue. When we moved the log/data files to SQL Servers default location (where tempdb, master,
> model, Northwind, and pubs live), we can attach it just fine from there.
> On further inspection, it appears that the SQL Server services are not running under its own (or even another's) domain user
> account. It looks like it is running under the Local System account.
> What are all the pro's & con's for using its own Domain User account vs using the Local System account?
> I remember in my SQL Server Admin class, our instructor had us set up its own Domain User account and use that. I don't
> think/remember if he explained why or if the book explains why, though.
> Thanks again for all of your help,
> Conan
By running SQL under a domain account, you have greater control over
permissions. Also, if you need to access any network resources, for
doing backups to a remote drive for instance, you'll need to be running
under a domain account.
Tracy McKibben
MCDBA
http://www.realsqlguy.comsql