<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2008
»
SQL Server 2008 - General
»
OS permission override by SQL Server
OS permission override by SQL Server
Rate Topic
Display Mode
Topic Options
Author
Message
S_Kumar_S
S_Kumar_S
Posted Friday, September 28, 2012 5:42 AM
Old Hand
Group: General Forum Members
Last Login: Today @ 7:59 AM
Points: 322,
Visits: 719
Hi
My windows ID has admin rights on the sql server but I am not the administrator of the server. Now there is database of which I am owner. but the mdf and ldf files of this database are in a directory to which I don't have access, means I can't copy those files if database is detached.
is there a way to override OS permission through Sql server so that I may copy those files?
-----Table Proc Index Performance TSQL &&%$#@*(#@$%.......------------
Deep Into SQL Jungle
Post #1365801
Jason-299789
Jason-299789
Posted Friday, September 28, 2012 5:47 AM
SSC Eights!
Group: General Forum Members
Last Login: Today @ 10:31 AM
Points: 836,
Visits: 2,192
I would speak to the network admin about obtaining rights as those folders are locked down for a reason, and either ask someone with the correct privlages to copy them or make a case of having permissions to those folders.
Out of curiosity why isnt a backup/restore a valid option?
_________________________________________________________________________
SSC Guide to Posting and Best Practices
Post #1365805
S_Kumar_S
S_Kumar_S
Posted Friday, September 28, 2012 7:00 AM
Old Hand
Group: General Forum Members
Last Login: Today @ 7:59 AM
Points: 322,
Visits: 719
we just want to shift files from one drive to another with larger space. So attach/ detach looks simpler...
Jason-299789 (9/28/2012)
I would speak to the network admin about obtaining rights as those folders are locked down for a reason, and either ask someone with the correct privlages to copy them or make a case of having permissions to those folders.
Out of curiosity why isnt a backup/restore a valid option?
-----Table Proc Index Performance TSQL &&%$#@*(#@$%.......------------
Deep Into SQL Jungle
Post #1365844
Jason-299789
Jason-299789
Posted Friday, September 28, 2012 7:14 AM
SSC Eights!
Group: General Forum Members
Last Login: Today @ 10:31 AM
Points: 836,
Visits: 2,192
Fair enough, just curious why the backup wasnt an option, and a detach in this situation is the best way of doing it.
I would still find out why access is restricted to those drives/folders, as it sounds more like a security measure than anything else.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
Post #1365850
dan-572483
dan-572483
Posted Friday, September 28, 2012 12:08 PM
Old Hand
Group: General Forum Members
Last Login: Friday, June 14, 2013 3:11 PM
Points: 338,
Visits: 993
Wouldn't an ALTER DATABASE operation be better for this?
http://msdn.microsoft.com/en-us/library/bb522469.aspx
You would still need access to the drives where the files are located.
Post #1366060
S_Kumar_S
S_Kumar_S
Posted Monday, October 01, 2012 4:33 AM
Old Hand
Group: General Forum Members
Last Login: Today @ 7:59 AM
Points: 322,
Visits: 719
Well one reason is tha tI don't want users to enter any data in this database while I am moving it. I know there are other ways to achive it but attach ditch just does all I needned to achive.
S_Kumar_S (9/28/2012)
we just want to shift files from one drive to another with larger space. So attach/ detach looks simpler...
Jason-299789 (9/28/2012)
I would speak to the network admin about obtaining rights as those folders are locked down for a reason, and either ask someone with the correct privlages to copy them or make a case of having permissions to those folders.
Out of curiosity why isnt a backup/restore a valid option?
-----Table Proc Index Performance TSQL &&%$#@*(#@$%.......------------
Deep Into SQL Jungle
Post #1366469
MysteryJimbo
MysteryJimbo
Posted Monday, October 01, 2012 8:18 AM
Ten Centuries
Group: General Forum Members
Last Login: Today @ 2:25 AM
Points: 1,125,
Visits: 14,518
You could create a SQL Account with sysadmin permissions and use this to do the detach/attach. It should work if the service account has host admin priviledges but it is a work around to circumvent security.
The correct way should be to get the server admin to grant you permissions to the required folders.
Post #1366595
SpringTownDBA
SpringTownDBA
Posted Monday, October 01, 2012 10:03 AM
Old Hand
Group: General Forum Members
Last Login: Thursday, June 06, 2013 10:38 AM
Points: 316,
Visits: 1,488
Sql server should have permissions to copy the file, so xp_cmdshell.
My approach for moving databases:
1. Alter DB set single_user rollback immediate
2. Alter db set offline
3. xp_cmdshell to copy mdf/ldf to new location, if error, goto 6.
4. xp_cmdshell to rename old files from data.mdf to data_old.mdf
5. Alter DB to point to files on new drive.
6. Alter db set online
7. Alter db set multi_user
Post #1366642
S_Kumar_S
S_Kumar_S
Posted Wednesday, October 03, 2012 3:59 AM
Old Hand
Group: General Forum Members
Last Login: Today @ 7:59 AM
Points: 322,
Visits: 719
Exactly what I did, but the copy permission was not there :)
SpringTownDBA (10/1/2012)
Sql server should have permissions to copy the file, so xp_cmdshell.
My approach for moving databases:
1. Alter DB set single_user rollback immediate
2. Alter db set offline
3. xp_cmdshell to copy mdf/ldf to new location, if error, goto 6.
4. xp_cmdshell to rename old files from data.mdf to data_old.mdf
5. Alter DB to point to files on new drive.
6. Alter db set online
7. Alter db set multi_user
-----Table Proc Index Performance TSQL &&%$#@*(#@$%.......------------
Deep Into SQL Jungle
Post #1367514
S_Kumar_S
S_Kumar_S
Posted Wednesday, October 03, 2012 3:59 AM
Old Hand
Group: General Forum Members
Last Login: Today @ 7:59 AM
Points: 322,
Visits: 719
I second your thoughts. Correct way is to get permissions and I have already asked for that.
MysteryJimbo (10/1/2012)
You could create a SQL Account with sysadmin permissions and use this to do the detach/attach. It should work if the service account has host admin priviledges but it is a work around to circumvent security.
The correct way should be to get the server admin to grant you permissions to the required folders.
-----Table Proc Index Performance TSQL &&%$#@*(#@$%.......------------
Deep Into SQL Jungle
Post #1367516
« Prev Topic
|
Next Topic »
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
