1

I have a script that detaches databases for archival when they meet certain criteria. The script is executed by SSIS under the local Administrator account by the Ghost of DBA Past. Per design it (by this I meant detaching databases in general) removes all other ownership and makes ownership exclusive to the local Administrator account. Here's where it gets weird:

I can't seem to change permissions back except via two methods:

  1. Windows GUI properties/Security etc.
  2. Copying the files to a sub directory that has inherited different permissions.

What I can't get to work:

  1. PowerShell Set-Acl
  2. PowerShell Move-Item
  3. Moving/cutting the files in Windows GUI
  4. icacls

I understand why ownership changes, but not why it's so difficult to edit the permissions afterwards, and why the files seem to essentially be hidden from command line tools.

Reason for the change is that sometimes data needs to be put back online before it goes to tape for whatever reason, and I can't attach the database(s) without changing permissions first.

Thanks

  • 1
    Why are you archiving databases by detaching them instead of taking a backup? A copy of the MDF file is NOT a backup. Barring a proper backup (which should solve the problem), why don't you defer the permissions change to closer to the point where you know you're going to move the file to tape? Who has access to this machine that they're going to be able to do something with an .mdf file? Who exactly owns these files before you make the permissions change? – Aaron Bertrand Sep 25 '14 at 20:27
  • Like I said, Ghost of DBA Past. Wasn't my idea, I just inherited it when I started here several months ago. Our backups are via a 3rd party app with two week data aging. These files go to disk along with related project data and get archived to tape that way. I will bring taking backups instead, but for some reason that freaked my boss out when I brought it up before. p.s. Recently bought 10 SQL Sentry licenses. Love the product. – Erik Darling Sep 25 '14 at 20:55
  • @MikeWalsh has an excellent answer to a similar question I posed a while ago. The situation was I couldn't attach a database using SQL Server Management Studio without first elevating to "Administrator" through runas. dba.stackexchange.com/questions/53756/… – Max Vernon Sep 25 '14 at 20:55
  • You might also look at this answer on SO regarding takeown.exe. – Shawn Melton Sep 26 '14 at 2:16
  • I ended up trying that shortly after posting. Like other CLI solutions it couldn't access the files, even logged in and run in a PS window with Administrator privileges. The only thing that consistently works is copying to a new subdirectory with correct permissions set, and then deleting the original files. – Erik Darling Sep 26 '14 at 2:38
2

You should enable trace flag 1802 on your database servers. This will allow SQL Server to retain inheritable permissions when you detach a database so you won't experience this issue going forward:

http://support2.microsoft.com/kb/922804

DBCC TRACEON(1802, -1)

Add it to the startup parameters of the SQL Server service http://msdn.microsoft.com/en-us/library/ms190737(v=sql.110).aspx

  • Even if their process - by design - manually removes all permissions after the detach? I don't think a trace flag can help you there. – Aaron Bertrand Sep 25 '14 at 21:58
  • It might be worth a shot until I can get the okay to re-write this to use backups instead. The process doesn't explicitly change permissions at any point that I'm aware of. – Erik Darling Sep 25 '14 at 23:11
  • SQLDriver, did you mean that the process/application you are running to do this removed the permissions after detach? My understanding is that SQL Server is doing it. If SQL is doing it then my answer will solve your problem. – Alf47 Sep 29 '14 at 17:56
0

Maybe this is not an answer to your question but I believe it is relevant. I would write a comment if I could but I want to add the pictures and the script I used.

I had the same problem while ago, I was getting the following error message while trying to re-attach a database:

enter image description here

The way I sorted this out was impersonating the login used to detach the database and then run the following command: Note that I change the DB owner straight after.

USE [master]
GO
CREATE DATABASE [DEOrderArchive] ON 
( FILENAME = N'Y:\SQLDATA\REP\DEOrderArchive.mdf' ),
( FILENAME = N'Z:\SQLLogs\REP\DEOrderArchive.ldf' )
FOR ATTACH
GO
USE [DEOrderArchive]
GO
EXEC dbo.sp_changedbowner @loginame = N'sa', @map = false
GO

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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