April 15, 2015 at 9:35 am
arrghhh
Details:
Sql2008 R2
Windows 2008 R2
VM
Need to move files to different drive
Detatched successfully. However I physically cannot copy the files to the new folder, continuously getting "You need administrative permissions" (interestingly sometimes it tells me that I need my permission to move this file).
I'm Administrator on this server and the SQL instance. I changed ownership of the files to myself, but that did not help.
Command line copy/move also got 'access denied'
Other, non-SQL files copy freely between the folders. I have full rights on both folders and all the files.
Tried logging into the server using SQL server's user AD account. Still could not copy/move.
My boss also tried the copy and encountered the same issue. I'm not sure if this is an SQL issue or a windows issue.
...
-- FORTRAN manual for Xerox Computers --
April 15, 2015 at 10:15 am
Are you trying to copy from SSMS using xp_cmdshell or from Windows Explorer via RDP to the box?
April 15, 2015 at 10:59 am
I'm using windows through RDP
...
-- FORTRAN manual for Xerox Computers --
April 15, 2015 at 2:11 pm
Detach now (since SQL 2005, IIRC) changes the security on the files -- this is an intentional thing by MS, as a "security feature". But it's been a royal p.i.t.a. to me! It's extremely likely that the new owner does not have permissions to the directory.
I guess the "bright side" is it seems to put the old security back once the file is successfully reattached.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
April 15, 2015 at 2:14 pm
ScottPletcher (4/15/2015)
Detach now (since SQL 2005, IIRC) changes the security on the files -- this is an intentional thing by MS, as a "security feature". But it's been a royal p.i.t.a. to me! It's extremely likely that the new owner does not have permissions to the directory.I guess the "bright side" is it seems to put the old security back once the file is successfully reattached.
Actually I took ownership of the file, I have full rights on destination and source. But you're right, there are some crazy things going on for 'security*'
*Security through making it impossible to use.
...
-- FORTRAN manual for Xerox Computers --
April 15, 2015 at 2:18 pm
jay-h (4/15/2015)
I'm Administrator on this server and the SQL instance.
This does not guarantee access to the file(s)
right click each file and select the properties then switch to security tab, check the ACLs here
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 16, 2015 at 11:36 am
Ok, I found some issues in the ACL permissions. After that I was able to copy.
thx
...
-- FORTRAN manual for Xerox Computers --
April 16, 2015 at 12:43 pm
jay-h (4/15/2015)
arrghhhDetails:
Sql2008 R2
Windows 2008 R2
VM
Need to move files to different drive
Detatched successfully. However I physically cannot copy the files to the new folder, continuously getting "You need administrative permissions" (interestingly sometimes it tells me that I need my permission to move this file).
I'm Administrator on this server and the SQL instance. I changed ownership of the files to myself, but that did not help.
Command line copy/move also got 'access denied'
Other, non-SQL files copy freely between the folders. I have full rights on both folders and all the files.
Tried logging into the server using SQL server's user AD account. Still could not copy/move.
My boss also tried the copy and encountered the same issue. I'm not sure if this is an SQL issue or a windows issue.
Don't!
The company I work for now, used to use this method to move databases and SQL files. As mentioned already, the ACL and NTFS permissions may change which makes difficult to re-attach.
What I do instead if putting the database offline, edit the metadata via TSQL, and move the ndf or mdf files. Then put the database back online. This retains permissions, statistics, and other important information already saved inside the database and MSSQL.
Last but not least, sp_attach is deprecated in SQL2014 so it may be a good idea start getting yourself more familiar with a different approach.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply