locked
SQL Server Security settings for particular database RRS feed

  • Question

  • we have an application with sql database. but when we are installing to client site the administrators can access our database and edit tables.

    is  there any settings to deny the administrator to access database by using management studio or any other database editing softwares.

    Friday, June 3, 2011 6:33 AM

Answers

  • You cannot keep the administrator of a computer out of the SQL Server Database Engine. See Troubleshooting: Connecting to SQL Server When System Administrators Are Locked Out. Your protection for your intelectual property is in the license for your application. If you are the domain admin, you can make sure that local administrators will leave an audit trail if they access the database. But you can't keep them out. The steps listed above can make it difficult. And that will keep out most people.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    • Proposed as answer by Peja Tao Tuesday, June 7, 2011 2:50 AM
    • Marked as answer by Peja Tao Tuesday, June 14, 2011 8:44 AM
    Friday, June 3, 2011 4:06 PM

All replies

  • Hi,

    Well, it is a question where the answer comes with compromises. Here is an idea, may not follow best practices:

    • check that you have mixed mode authentication,
    • make sure the sa user has strong password or disable it and create another sql login with strong password then add it to the sysadmin server role.
    • remove the windows users from the sysadmin server roles
    • do not share the sa password with the client's administrators
    • make sure that you assigned the minimum required permission for all other users

    In this scenario you win the management of the SQL instance :) as the client's administrators cannot connect to the SQL instance with administrative privileges.

    I hope it helps.

    J.


    There are 10 type of people. Those who understand binary and those who do not.
    Friday, June 3, 2011 8:49 AM
  • Hi,

     

    1) What Authentication is the Clinet administrator connecting(SQL Auth or Windows)?

    2) Now on SQL Server Management Studio in your main server, connect to Logins ie Seurity - Logins - select the particular login's properties, then User Mapping and select the database which you want to revoke access, give data reader access to the Login, so he will only be able to read the data and will not be able to modify the tables in the Database.

    3) Else if you dont wnat the login to read anything from the database then you can choose, deny data reader, which will revoke all his reading abilities on the database.

     

    Let me know if that is working and solves your issue.

     

    Regards,

    RGP

     

     

    Friday, June 3, 2011 10:26 AM
  • The authentication is windows. the client means our customer not the client system.we need to block administrator account(server or client) to acceessing database.

    Friday, June 3, 2011 10:37 AM
  • What version of SQL Server are you using?  If SQL 2005 and earlier, you can remove the BUILTIN\ADMINISTRATOR account from the sysadmin fixed server role.  Domain users, local users that are member of the servers local administrator group have access to SQL Server via the BUILTIN\Admistrator login.  Take care when removing it though, as it can have undesired consequences if not done carefully:

    http://www.sqlservercentral.com/articles/Security/removingthebuiltinadministratorssomepitfallstoavoi/1661/ - Kathi Kellenberger on SQL Server Central has written a good case of what can happen...

     

    Friday, June 3, 2011 12:13 PM
  • we are using sql server 2005. is there any code to deny access to a particular database for administrator.

    Friday, June 3, 2011 12:26 PM
  • You cannot keep the administrator of a computer out of the SQL Server Database Engine. See Troubleshooting: Connecting to SQL Server When System Administrators Are Locked Out. Your protection for your intelectual property is in the license for your application. If you are the domain admin, you can make sure that local administrators will leave an audit trail if they access the database. But you can't keep them out. The steps listed above can make it difficult. And that will keep out most people.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    • Proposed as answer by Peja Tao Tuesday, June 7, 2011 2:50 AM
    • Marked as answer by Peja Tao Tuesday, June 14, 2011 8:44 AM
    Friday, June 3, 2011 4:06 PM