SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


OS permission override by SQL Server


OS permission override by SQL Server

Author
Message
S_Kumar_S
S_Kumar_S
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4865 Visits: 1101
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?

Online Trainer For SQL DBA and Developer @RedBushTechnologies with 16 yrs exp.
Jason-299789
Jason-299789
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12437 Visits: 3232
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
S_Kumar_S
S_Kumar_S
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4865 Visits: 1101
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?


Online Trainer For SQL DBA and Developer @RedBushTechnologies with 16 yrs exp.
Jason-299789
Jason-299789
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12437 Visits: 3232
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
dan-572483
dan-572483
SSCrazy Eights
SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)

Group: General Forum Members
Points: 9093 Visits: 2026
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.
S_Kumar_S
S_Kumar_S
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4865 Visits: 1101
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?


Online Trainer For SQL DBA and Developer @RedBushTechnologies with 16 yrs exp.
MysteryJimbo
MysteryJimbo
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13367 Visits: 15346
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.
SpringTownDBA
SpringTownDBA
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3968 Visits: 1499
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
S_Kumar_S
S_Kumar_S
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4865 Visits: 1101
Exactly what I did, but the copy permission was not there Smile
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



Online Trainer For SQL DBA and Developer @RedBushTechnologies with 16 yrs exp.
S_Kumar_S
S_Kumar_S
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4865 Visits: 1101
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.


Online Trainer For SQL DBA and Developer @RedBushTechnologies with 16 yrs exp.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search