December 6, 2011 at 2:05 pm
I am running sql2005 standard in a VM environment (this is a dev server).
We are moving some databases to another drive. I have a group of 4 that are part of one application.
I detached all 4 databases and went to move them to the new drive. I got "access is denied" errors with "make sure the disk is not full or write protected". It was almost as if SQL server was still holding on to them though they were definitely detached. I tried these tricks
Restarted SQL service
restarted server
stopped SQL service and attempted move while service was stopped
NONE of thse allowed the files to be moved.
I worked with someone else here and we changed ownership to me, and after some playing about were able to move the files.
I went to reattach the databases but the databases came up 'read only', when trying to get properties of the db, I got intermittent 'access denied' errors, which probably explains why the attachment came up as read only. I detached the db again.
With the db in the new location, with ownership and full adminstrative rights, I still cannot copy or move these files. I continue to get the same access denied errors.
It is not possible in any way that I can see that any other program has access to these files.
Any thoughts???
Jay
...
-- FORTRAN manual for Xerox Computers --
December 6, 2011 at 2:09 pm
windows 2008 by any chance? If so Its the bloody stupid User Access controls, I would disable that feature.
Changing the ownership of the files should be the answer, on the destination server change the ownership to the SQL service account.
---------------------------------------------------------------------
December 6, 2011 at 2:09 pm
http://msdn.microsoft.com/en-us/library/ms189128.aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 6, 2011 at 2:12 pm
Thanks for the quick responses, but this is W2K3 sp2
...
-- FORTRAN manual for Xerox Computers --
December 7, 2011 at 4:46 am
I used to get this problem a lot, and the article that Gail provided is great.
We where in a multi domain environment, parent, child eg
domain1.com
subdomain1.domain1.com
subdomain2.domain1.com
There where servers in all 3 domains but all client machines sat in subdomain2.
If I was to execute sp_detach_db on a database on subdomain1 or the root domain, from my client in subdomain2, it would lock the files to my subdomain2 account.
Do when I RDP'd to domain1 server using my domain1 account I couldn't do anything with the files, but if I logged on to domain1 server using my subdomain2 account, I could do what ever I wanted with the files.
This also happens should you detach a DB from a client and then login as a different account to the server to copy the files eg the servers administrator as it has locked the files to you, so you dont have to be in a multi domain environment like I was, to get around it you need to assign permissions to the files or login as the account who has detached the DB
December 7, 2011 at 6:46 am
Again thanks for the suggestions, though it appears that not yet describe my situation.
I am in the same domain as the server, I have full server admin rights and sysadmin on the SQL, and am the owner of the files.
The problem seems to be more at the OS level because even from windows explorer I cannot copy or move the files almost as if they are in use, but they are not in use. I have occasionally seen a file erroneously flagged as in use, but a reboot always cleared that. That is not working in this case.
...
-- FORTRAN manual for Xerox Computers --
December 7, 2011 at 6:55 am
have a look for a program called handle from SysInternals, this should tell you which processes have locks on files, so you can cleanly stop them to release any OS locks on the files.
if nothing comes from the above did you detach the DB from a client or on the server? if on the client, reattach it, then detach it from the server and copy it in the same RDP session, just incase something went wrong on the detach and permission change on the files.
December 7, 2011 at 8:03 am
Seems solved.
These files (but not the containing folder) seemed to be missing explicit administrative permissions. I could not move or copy them, but could change security (go figure), once I added the administrator's group, the files became usable.
...
-- FORTRAN manual for Xerox Computers --
December 7, 2011 at 8:08 am
that links with what the article was saying
the db was detached by user1, SQL then gives only user1 access to the files of the database anyone else including administrators are locked out (except to change permissions)
if user2 comes and trys to move the files then they get access denied
an administrator of the server where the files live, can go in an edit the security so that user2 can then do what they need to do with the files
so if you detach using user1, log on to the server using user1 you dont get this issue
in my example i was detaching as subdomain2\AntG and loging in as domain1\AntG to which my domain1 account didnt have access to the files until i either changed the access or logged in as subdomain2\AntG
December 7, 2011 at 8:24 am
That's very interesting. It seems like a flaw in the security model, but that does make sense insofar as what was happening.
I wonder if there is some hack/attach that someone could use if they detached the files? You need fairly high permissions to detach anyway, so perhaps it isn't an issue, but it seems shortsighted to not allow other admins to move/attach/copy the files.
December 7, 2011 at 8:32 am
I have never tried it using a SQL login, to see what actually happens with the file locking, but thats always been the case with Windows logins.
Saying that I will give it a go.
December 7, 2011 at 8:38 am
right, just tried this on our prod server as its externally hosted and we can only use SQL logins as its not tied to the domain
created new DB AntDetachTest
sp_detach_db AntDetachTest
RDP'd to the box and was able to copy the MDF and LDF using the local servers Admin account, so seems like it assigns Administrators and the MSSQLSERVER access group the rights needed after looking at the security of the files.
So if detaching by Windows accounts, login to the server as the same windows account, otherwise use SQL logins, might not always be an option, especially for servers which are set for Windows only authentication.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply