Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

OS permission override by SQL Server Expand / Collapse
Author
Message
Posted Friday, September 28, 2012 5:42 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 3:34 AM
Points: 378, Visits: 886
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
Posted Friday, September 28, 2012 5:47 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, July 18, 2014 9:09 AM
Points: 870, Visits: 2,385
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
Posted Friday, September 28, 2012 7:00 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 3:34 AM
Points: 378, Visits: 886
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
Posted Friday, September 28, 2012 7:14 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, July 18, 2014 9:09 AM
Points: 870, Visits: 2,385
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
Posted Friday, September 28, 2012 12:08 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 2:50 PM
Points: 550, Visits: 1,611
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
Posted Monday, October 1, 2012 4:33 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 3:34 AM
Points: 378, Visits: 886
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
Posted Monday, October 1, 2012 8:18 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, August 18, 2014 3:04 AM
Points: 1,362, Visits: 15,269
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
Posted Monday, October 1, 2012 10:03 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, April 1, 2014 3:26 PM
Points: 316, Visits: 1,497
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
Posted Wednesday, October 3, 2012 3:59 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 3:34 AM
Points: 378, Visits: 886
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
Posted Wednesday, October 3, 2012 3:59 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 3:34 AM
Points: 378, Visits: 886
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 »

Add to briefcase

Permissions Expand / Collapse