How security is set on drives of Windows 2003 Server to let only specific  SQL Server acces to them?

Posted on 2009-12-16
Last Modified: 2012-05-08
How security is set on drives of Windows Server 2003 to let only specific  SQL Server acces to them?

For exemple:

- Windows 2003A, has SQL Server 2005 installed and has those drives: C, D, E,F
- Windows 2003B, has also SQL server 2005 installed and SQL Management Studio

Let's say:

1- I open SQL Management Studio on Windows 2003B and connect on SQL databases of  Windows 2003A

2- When I try to restore a database from file, I can only acces to E and F drives of Windows 2003A

How security is set to get only acces to specific drives?
Question by:SAM2009
    LVL 2

    Expert Comment

    Use windows users with specific rights and priviledges on SQL server.
    i.e.  Grant connect on SQL server installed on  Windows 2003B and  Windows 2003A
    And Grant db_backupoperator SQL server installed on Windows 2003A and file permissions on E and F drives of Windows 2003A.

    LVL 5

    Expert Comment

    Do you use a windows domain account or a local account?
    On your SQL Servers, is it SQL Server authentication or Mixed mode authentication?

    The thing is if you want to restore database file which are located on Windows 2003A,whateever the drive is, you must share the folder containing your restore files; and the user you are using to connect to Windows 2003A should have access to both Windows 2003A and Windows 2003B.

    Any of your windows drive is protected by NTFS permissions until you share it ! Default permissions are set to let LOCAL MACHINE Users to acces the drives, meaning that if you are on Windows 2003A, all users(who are administrators of course) can access all drives. But by changing any of those NTFS permissions, you can add/remove a users, and therefore all/deny him the access to a drive.

    Then when you share a drive, you set SHARE PERMISSIONS which are different from NTFS Permissions. Share Permissions allow you to tell WHO on your NETWORK can See, Read or Write you shared folder. NTFS permissions defines who can ACCESS your shared folder.

    e.g: If you set permissions of User A to Write in your shared folder, and then on the NTFS permissions you deny him, he will see the folder but won't be able to Write into it !

    Hope this answered your question.
    LVL 1

    Accepted Solution

    Ya I already checked NTFS searching I realized that SQL servers are set in cluster the drives are shared by the ressources in windows cluster manager... I forgot that.

     Thanks anyway for your help.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now