locked
securing SQL Server data within Windows RRS feed

  • Question

  • hi folks, is it possible for a Windows Server 2012 Standard   *admin* to *not* be granted access to SQL Server data or stored procedures (on the same box) in SQL 2014 Standard Edition?  If I'm not mistaken, a Windows Server admin can easily elevate his/her own rights to a full SQL admin.

    thanks much,
    Cos


    cos

    Sunday, March 12, 2017 7:53 PM

Answers

  • Hi cos,

    >>is there any additional way to further protect or rather, prevent Windows Admins from starting SQL Server in single-user mode?

    I’m afraid the answer is no, and that’s how you regain sysadmin access when you accidently lock yourself out of the system.

    If you have any other questions, please let me know.

    Regards,
    Lin

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by CosminI Wednesday, March 29, 2017 8:55 PM
    Tuesday, March 14, 2017 7:00 AM

All replies

  • A windows admin isn't automatically granted access to SQL server. You would have to manually add him/her to the sysadmin role. If they have a login to SQL Server and are not in the sysadmin role, they will not be able to evaluate their own rights with SQL Server.
    Sunday, March 12, 2017 8:16 PM
  • A windows admin isn't automatically granted access to SQL server. You would have to manually add him/her to the sysadmin role. If they have a login to SQL Server and are not in the sysadmin role, they will not be able to evaluate their own rights with SQL Server.

    No, this is not correct. If you are admin on the box, you can stop SQL Server and start it in single-user mode. Now you will be sysadmin in SQL Server.

    Sunday, March 12, 2017 8:34 PM
  • I should clarify that when Hilary says A windows admin isn't automatically granted access to SQL server, this is correct for normal circumstances. But as I said, not when you start SQL Server in single-user mode.

    Sunday, March 12, 2017 8:36 PM
  • thanks  --  based on the latest feedback of starting the SQL Server in single-user mode, is there any additional way to further protect or rather, prevent Windows Admins from starting SQL Server in single-user mode?  I think, say, starting windows in Safe Mode, etc, might still create a loophole to allow admins into SQL.     

    thanks much for any additional insight.


    cos


    • Edited by CosminI Monday, March 13, 2017 11:09 PM
    Monday, March 13, 2017 11:08 PM
  • Hi Cos,

    By default in MSSQL 2012, there is no access by default for Windows administrators to your SQL Server. There is no workaround to prevent Windows (local or domain) from tweaking the machine configuration or services (the OS layer) on which your SQL instance is hosted.

    Don't add random people (if you don't really trust them) to the Windows Administrator Group.

    Cheers,

    Sunit


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped.

    Tuesday, March 14, 2017 12:18 AM
  • Hi cos,

    >>is there any additional way to further protect or rather, prevent Windows Admins from starting SQL Server in single-user mode?

    I’m afraid the answer is no, and that’s how you regain sysadmin access when you accidently lock yourself out of the system.

    If you have any other questions, please let me know.

    Regards,
    Lin

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by CosminI Wednesday, March 29, 2017 8:55 PM
    Tuesday, March 14, 2017 7:00 AM
  • You cannot prevent the computer admin from getting access. But you can configure the actions to be audited. And in a domain environment, the audit can be in a location that is not available to the computer admin. (Presuming they are not a domain admin.)

    If you are installing your SQL Server application on someone else's computer, see Protecting Your SQL Server Intellectual Property https://msdn.microsoft.com/en-us/library/mt778968.aspx


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Tuesday, March 14, 2017 3:27 PM