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
Say Hey Kid
Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)

Group: General Forum Members
Points: 670 Visits: 1062
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
Jason-299789
Jason-299789
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2065 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
Say Hey Kid
Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)

Group: General Forum Members
Points: 670 Visits: 1062
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
Jason-299789
Jason-299789
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2065 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
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1318 Visits: 1985
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
Say Hey Kid
Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)

Group: General Forum Members
Points: 670 Visits: 1062
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
MysteryJimbo
MysteryJimbo
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2207 Visits: 15344
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
Mr or Mrs. 500
Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)

Group: General Forum Members
Points: 580 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
Say Hey Kid
Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)

Group: General Forum Members
Points: 670 Visits: 1062
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



-----Table Proc Index Performance TSQL &&%$#@*(#@$%.......------------
Deep Into SQL Jungle
S_Kumar_S
S_Kumar_S
Say Hey Kid
Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)

Group: General Forum Members
Points: 670 Visits: 1062
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
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