Default NTFS permissions on new DB

  • Gift Peddie

    SSC Guru

    Points: 73570

    (That's because SQL Server 2005 intentionally breaks inheritance for the data files.)

    The desires of the Windows file system team should not be imposed on people spending money to buy a full featured RDBMS.  I am saying it as I always say when I see invalid implementations it is not valid to let Network Admin control what goes on in a SQL Server folder.  We will not be having this conversation if it is Oracle because Oracle does not make OS.  Microsoft have spent millions to remove SQL Authentitication but somebody have to pay for all the math needed to replace it.

    Kind regards,
    Gift Peddie

  • Ross Biddle

    Old Hand

    Points: 315

    What process would you recommend for moving 2 terabytes of Data from a dev to prod environment within a relatively small change control window ( <4 hours)

    The process must be repeatable and flexible (ie - moving databases such as this using multiple container files etc. at any time during the day and many times during a given day)

    I am willing to learn a new technique - but so far - detach, copy, and attach has proven effective in our environment (dozens of developers moving terabytes of data from 50ish development SQL 2000/2005 servers to a dozen or so production SQL 2005 servers)

  • K. Brian Kelley

    SSC Guru

    Points: 114465

    What is being asked for is not an invalid implementation. It does not, in any way, impair SQL Server from working. If it did, your argument would have basis. What SQL Server 2005's behavior does do is impair the manageability of those database servers, even for legitimate DBA-related activities, when there isn't really a gain on the security side. Security is always about trade-offs and I can understand why the SQL Server did what it did. However, I happen to disagree with that choice and apparently someone had second thoughts if there's now a hotfix for detach/attach.

    Gift Peddie, let me assure you that this isn't just some "network admin" wanting to impose control without having thought this through. If you go back and look just a wee bit, you'll see I have a pretty solid background in SQL Server, especially in SQL Server development, performance monitoring, and security (the latter is what I've concentrated on for the last few years). If you have any doubts to the that, please check my article list on this site. I don't point to it to blow my own horn so much as to point out that I've looked at it from an angle more than just as a network/security admin, which is the strawman you're trying to use.

     

    K. Brian Kelley
    @kbriankelley

  • Gift Peddie

    SSC Guru

    Points: 73570

    You have written about it fine but Network Admin or Security Admin in charge of data so the questions is what do they know about data? actually nothing yes some choose to know like you but in general they are not required to know and the Windows team choose to give them control over something they don't know because Microsoft makes the operating system.

     

    Kind regards,
    Gift Peddie

  • K. Brian Kelley

    SSC Guru

    Points: 114465

    They don't have to know anything about the data. To say that they do is incorrect. This is what they have to know with respect to those files in the context of what we've been discussing:

    • The data is contained in two or more files (not just the .mdf).
    • Those files are used by SQL Server and by default stay open (meaning they can't be backed up without an open files agent).
    • The SQL Server service account will need access to those files.

    To say the network admin has to know the data is like saying a SQL Server DBA must understand how Active Directory replication works. While some DBAs might need to know that, it's not in the typical DBA skillset. So the average network admin shouldn't be expected to know the inner workings of SQL Server.

    With that said... so long as the network admin understands the 3 points I've made above, he or she can ensure access to the data files aren't an issue for SQL Server.

     

    K. Brian Kelley
    @kbriankelley

  • Gift Peddie

    SSC Guru

    Points: 73570

    (To say the network admin has to know the data is like saying a SQL Server DBA must understand how Active Directory replication works. While some DBAs might need to know that, it's not in the typical DBA skillset. So the average network admin shouldn't be expected to know the inner workings of SQL Server.)

    DBAs are not trying to control what Network Admins do but Network Admins in the name security is trying to control what DBAs do and that is invalid. This is the reason Oracle over writes all Windows settings in Windows installation now I know.

    You compared IIS a Windows component to something that cost twice the price of Windows so this conversation is not going to go any where. Oracle 11 is in Beta 1 so the Windows team will not be allowed to do what you think they should do because I am saying it is invalid.  Note to self sign on for Lorghorn beta testing.

    Kind regards,
    Gift Peddie

  • Ross Biddle

    Old Hand

    Points: 315

    Kind of amusing that this is the angle of discussion as all of the DA's are the ones clamoring for the NTFS permissions so that they can do their jobs.  Us "Network Admins" have dedicated admin accounts that have full access to all the files - so its a moot point for us...

    Still waiting for a better way to do things...

  • bernard.baeten

    SSC Rookie

    Points: 38

    2015 and still a problem NTFS permissions are lost when restorng a bak file on another server.

    the users on the new server cannot even see the LDF and MDF files even when they are attached.

    sometimes the owner flied of the files are empty

    is there a solution for this problem without having admin rights

Viewing 8 posts - 16 through 23 (of 23 total)

You must be logged in to reply to this topic. Login to reply