DB attaches as read only

Hello all,
I detached one of our DB's from one of our servers, copied the data & log fi
les 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 d
rive, 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 t
o 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 file
s 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 runni
ng 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 usin
g the Local System account?
I remember in my SQL Server Admin class, our instructor had us set up its ow
n 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.co
m...
> Conan Kelly wrote:
> 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 fi
les 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 run
ning 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 us
ing 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.com

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 Kelly
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
|||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:
> 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.com

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

DB and Log size Checking

Hi,
I am wondering what the best way is to go about getting data about
database size (free & used space). I have multiple servers that I
must keep an eye on so I am developing a little application that will
report on the DB and log size but I do not want to go to each server
with enterprise manager or query analyzer to check on them. As far as
log size information I have been using a stored procedure that retuns
results from DBCC SQLPERF(Logspace) but I have not been able to find a
similar function for the database stats. Any ideas?
Paul,
For whatever the current database is the system stored procedures:
sp_spaceused -- shows utilization
sp_helpfile -- shows the file sizes, e.g. the mdf and the ldf, etc.
I use these both to understand what is happening.
Russell Fields
"Paul T." <weluvpaul@.hotmail.com> wrote in message
news:1fe3e09e.0408231149.1aa9225a@.posting.google.c om...
> Hi,
> I am wondering what the best way is to go about getting data about
> database size (free & used space). I have multiple servers that I
> must keep an eye on so I am developing a little application that will
> report on the DB and log size but I do not want to go to each server
> with enterprise manager or query analyzer to check on them. As far as
> log size information I have been using a stored procedure that retuns
> results from DBCC SQLPERF(Logspace) but I have not been able to find a
> similar function for the database stats. Any ideas?
|||Thank you Russel!

DB access revoked...

I have two servers (A & B) with 7 DBs and have created a
user on each server with DB_OWNER role.
When I restore a DB backup from one server A to B, the
user on server B, loses all its access to these 7 DBs from
a server level, though I can see that this user exists
from a DB perspective.
Any ideas why and how this can be corrected? Thanks.This is a multi-part message in MIME format.
--=_NextPart_000_0094_01C3810D.6E860260
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Check out sp_change_users_login in the BOL.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Rob" <rhchin@.hotmail.com> wrote in message =news:0dfc01c3812e$29ce71e0$a001280a@.phx.gbl...
I have two servers (A & B) with 7 DBs and have created a user on each server with DB_OWNER role.
When I restore a DB backup from one server A to B, the user on server B, loses all its access to these 7 DBs from a server level, though I can see that this user exists from a DB perspective.
Any ideas why and how this can be corrected? Thanks.
--=_NextPart_000_0094_01C3810D.6E860260
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Check out sp_change_users_login in the =BOL.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Rob" wrote in message news:0dfc01c3812e$29=ce71e0$a001280a@.phx.gbl...I have two servers (A & B) with 7 DBs and have created a user on =each server with DB_OWNER role.When I restore a DB backup from one =server A to B, the user on server B, loses all its access to these 7 DBs from =a server level, though I can see that this user exists from a DB perspective.Any ideas why and how this can be corrected? Thanks.

---

--=_NextPart_000_0094_01C3810D.6E860260--