none
How to minimize Remote DBA's Right?

    Question

  • 
    
    How to minimize Remote DBA's permission for data.  I would like to encript data  in database user level?   Thank you in anticipation.....
    
    Monday, January 20, 2014 7:41 PM

Answers

  • The question is difficult to answer without knowing what permissions the remote DBA will have, what is in his contract etc. Of course you can encrypt columns and trust him not to open the keys, but encrypting columns has effects on the application and is nothing you do at whim.

    Obviously, you should make sure that your organisation signs non-disclosure agreement with the remote DBA.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, January 20, 2014 10:40 PM

All replies

  • The question is difficult to answer without knowing what permissions the remote DBA will have, what is in his contract etc. Of course you can encrypt columns and trust him not to open the keys, but encrypting columns has effects on the application and is nothing you do at whim.

    Obviously, you should make sure that your organisation signs non-disclosure agreement with the remote DBA.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, January 20, 2014 10:40 PM
  • 1. In the server scope : get it just a public Role = without permissions on the server

    2. In the specific database scope : get it db_datareader to read all data and db_datawriter to (insert, update and delete data).

    3. you can create your database role with specifics permissions :

    This example dont have a select on the column (email) :

    USE [youDataBase]
    GO

    CREATE ROLE [yourRole]
    GO

    DENY SELECT ON [yourTable] ([email]) TO [yourRole]
    GO

    --Add your remote admin to the role :
    EXEC sp_addrolemember N'yourRole', N'yourRemoteAdmin'
    GO

    4. You can also use SQL Server Profiler to catch what's happen on the server and save them on the table or trace file .trc.

    Monday, January 20, 2014 11:43 PM
  • In addition to Erland's reply, if the Remote DBA connects as a member of sysadmin server role he/she are able to do anything on the instance.. You have to grant the only rights she/he needs depends on your business requirements.

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, January 21, 2014 9:16 AM
  • It depends on what exactly you are trying to do.

    By definition (as Uri said), a "DBA" would be a member of the sysadmin role, and you cannot restrict that user in any way. 

    If you are trying to give a "user" certain rights to do some advanced activites normally reserved for a DBA, that is a different question.  In some cases that can be done, and in others requires sysadmin role membership.

     

    Tuesday, January 21, 2014 2:40 PM
  • Also you can't keep out the owner of the computer (members of the local Administrators group on the computer). Perhaps you can tell us more about the scenario? 

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

    Tuesday, January 21, 2014 4:36 PM