1

I am routinely taking a BAK file and restoring it. As a result of this action, an MDF file is generated from it by MSSQL.

My problem is that after the detaching the database I need to be able to move the MDF file programmatically and the user that is going to do this needs to not be in the Administrators group or to be the Default SQL server account. Is it possible to configure MSSQL server to that when it creates an MDF file such as this the permissions of the file include a group of my own arbitrary choosing?

It seems logical that this an MSSQL server setting somewhere, that there is a list of permissions that get added to files when they are created but I am having a great deal of trouble getting Google to distinguish between this case and other similar but unrelated cases.

closed as off-topic by mustaccio, John aka hot2use, McNets, LowlyDBA, Erik Darling Nov 4 '17 at 15:50

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – mustaccio, John aka hot2use, McNets, LowlyDBA, Erik Darling
If this question can be reworded to fit the rules in the help center, please edit the question.

  • 3
    Why do you think you need permissions to SQL Server data files? Only the SQL Server service account should be able to access those files. – mustaccio Oct 31 '17 at 18:46
  • 3
    What do you mean "an MDF file is created with some set or permissions", if you're using filelistonly - there isn't an actual restore happening. Was that just a typo? – Sean Gallardy Oct 31 '17 at 20:24
  • @SeanGallardy-Microsoft I mean what I said, a BAK file is provided and an MDF is created in my SQLData location with the name of the database. – James Robinson Oct 31 '17 at 20:45
  • @mustaccio assume that there are reasons, maybe they are that I have a lot of other code that would need to be majorly refactored if I changed it to work they way you suggest. – James Robinson Oct 31 '17 at 20:56
  • 3
    1) You can't restore a database using just filelistonly, so technically your question can't happen which is why I asked if it was a typo. 2) There is no way to change the way SQL Server ACLs files, I really do wonder what you'd need to do that TSQL or the engine can't do directly to the database files which you'd need extra permissions. Assuming you're using NTFS, you can set windows level inheritance. – Sean Gallardy Oct 31 '17 at 23:29
1

By default .mdf file permissions are granted to Administrators Group and SQL Service Account when a database is restored.

  • This is precisely what I am observing, my question is do you know if there is a way to change this? – James Robinson Nov 1 '17 at 12:27
0

Ok so I personally do NOT recommend this course of action, and there almost always is a better way of doing this, but you could technically use Powershell to do what Sean Gallardy mentioned:

-Deattach the file from MS SQL Server

-Verify it's de-attached

-Change the NTFS permissions to grant move (delete/write) rights to the file.

-Move it via powershell.

-Re-attach it

It would be great if you could let the community know why you're stuck in this process. Sometimes it's out of our hands, but if the team has better background info they might be able to help you in a slightly less risky and sensitive movement.

Moving MDF files is not one of the best practices to do.

In older versions I've seen database corruption from moving it from fast disks to slow disks.

You don't get any of the compression benefits.

Permissions sometimes have to be changed.

You can have unintended side effects such as Service Broker being disabled.

At a minimum check out using the newer non depreciated commands.

Please just note if you perceive any sensitivity to your questions, the community is often just trying to help protect you from unintended consequences and/or needs more info.

  • Thanks for your offer of help Ali but this is not an answer to my question. If you know it is not configurable that would be a fine answer. However, the problem I have within the parameters I am able to solve it requires that I change the ownership of an MDF file. One option would be to run some code as the SQL server admin which does this, another option would be to have SQL server set different permissions when it creates MDF files, I am humbly asking if anyone knows how this is configured since the nuance is very difficult to explain to Google. Any help much appreciated. – James Robinson Nov 2 '17 at 20:05
  • Ok so if I understand properly, the steps laid out above in the powershell section are not helpful because you explicitly need to run this from within SQL Server using T-SQL, and even Powershell from SQL Server is not going to be an option in this case. Is that correct? If so, you're really reaching some hard limits. SQL Server is largely limited to xp_cmdshell and Powershell in regards to interfacing with the OS. Taking ownership or change file permissions from within SQL Server, which doesn't require powershell or xp_cmdshell isnt a feature. We cant even use PS as the SQL user in a job? – Ali Razeghi Nov 2 '17 at 21:02
  • @JamesRobinson sorry forgot to tag you in my follow up question, wanted to make sure I clearly understood the constraints you are facing. – Ali Razeghi Nov 3 '17 at 16:44
  • 1
    Hi Ali, I'm not trying to "change the permissions" so much as "change what permissions are set when creating a file". This is the nuance I can't explain to google. – James Robinson Nov 7 '17 at 22:46
  • ah gotcha thank you for the clarification @JamesRobinson. RDFozz's answer as you noted gave you what is set. The 'cleanest' way I could think to do this without a feature request to Microsoft (probably will be rejected) is to have a powershell script that monitors jobs if you have some critical jobs, perhaps disable the ones that aren't running, wait for the running ones to stop, then shut down the sql agent, change permissions, restart agent, enable jobs. This is assuming you might have jobs you need to gracefully watch and if they run long, you're ok with other jobs being blocked. – Ali Razeghi Nov 8 '17 at 3:33

Not the answer you're looking for? Browse other questions tagged or ask your own question.