locked
How to Restrict DBA from Read/Write But Still can perform Maintenance tasks RRS feed

  • Question

  • Hi All,

    I recently got an requirement from Client for Migration/Regular DBA Support of SQL Server Production Servers. BUT they asked to HIDE data from OFFSHORE DBA.

    Onsite DBA will have access for Emergency Work.I thought of below option:

    -> NO SYSADMIN but db_owner with db_denyread and db_denywrite. Explicit Access on msdb, master to perform DBA tasks like Profiler, Job Execution and Security tasks..

    Can you advise how to implement this so that Offshore should be able to handle regular Maintenance but CANNOT see Data inside DBs. Any Internal Tool from Microsoft OR any 3rd Party Tool..??

    Monday, November 5, 2012 3:58 PM

Answers

  • How do you define regular maintenance? The db_backupoperator fixed database role might be useful. See Permissions of Fixed Database Roles (Database Engine)http://msdn.microsoft.com/en-us/library/ms189612(SQL.105).aspx db_owner sounds like more than you want.

    You might have to add some specific permissions for some actions. See the permission poster for your version of SQL Server at http://go.microsoft.com/fwlink/?LinkId=229142


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

    Monday, November 5, 2012 5:13 PM
  • Hi Rick,

    Thanks for your reminder. I have modified my codes as following. Vivek1982s, please check whether the codes can achieve your requirements.

    -- Add user Test3 for login Test3 on database Test
    use Test;
    go
    
    create user Test3 for login Test3
    
    -- Add user Test3 to db_owner data base role, and deny reading and writing
    use Test;
    go
    
    exec sp_addrolemember db_backupoperator,Test3
    -- Please use Grant command to add other permission to the user.
    -- http://msdn.microsoft.com/en-us/library/ms187965.aspx
    
    -- Add login Test3 to dbcreator server role, in order to restore database
    use master;
    go
    
    exec sp_addsrvrolemember Test3,dbcreator 
    
    -- grant alter trace to login Test3, in order to use Profiler
    use master;
    go
    
    grant alter trace to Test3
    
    -- Add user Test3 to SQLAgentOperatorRole, in order to view jobs and create own jobs.
    use msdb;
    go
    
    create user Test3 for login Test3;
    exec sp_addrolemember SQLAgentOperatorRole ,Test3
    


    Allen Li

    TechNet Community Support

    Thursday, November 8, 2012 3:25 AM

All replies

  • How do you define regular maintenance? The db_backupoperator fixed database role might be useful. See Permissions of Fixed Database Roles (Database Engine)http://msdn.microsoft.com/en-us/library/ms189612(SQL.105).aspx db_owner sounds like more than you want.

    You might have to add some specific permissions for some actions. See the permission poster for your version of SQL Server at http://go.microsoft.com/fwlink/?LinkId=229142


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

    Monday, November 5, 2012 5:13 PM
  • Thanks Rick..

    This is what I followed as of now. Regular Maintenance means:

    1. DB Backup and restore 2. SQL Agent Jobs (SQLAgentOperator is not allowing modification for other users job) 3. Space Issues Resolution 4. DML, DDL Execution 5. Profiler (ALTER TRACE) and similar activities

    ALL Above WITHOUT Read/write on DB.

    Do we need to follow same process for DBA Group or we have any Tool which can handle the requirement more efficiently..??

    Monday, November 5, 2012 5:45 PM
  • Hi Vivek1982s,

    Please check whether the following codes can achieve your requirement (Note: I have a SQL Server login named Test3):

    -- Add user Test3 for login Test3 on database Test
    use Test;
    go
    
    create user Test3 for login Test3
    
    -- Add user Test3 to db_owner data base role, and deny reading and writing
    use Test;
    go
    
    exec sp_addrolemember db_owner,Test3
    exec sp_addrolemember db_denydatareader,Test3
    exec sp_addrolemember db_denydatawriter,Test3
    
    -- Add login Test3 to dbcreator server role, in order to restore database
    use master;
    go
    
    exec sp_addsrvrolemember Test3,dbcreator 
    
    -- grant alter trace to login Test3, in order to use Profiler
    use master;
    go
    
    grant alter trace to Test3
    
    -- Add user Test3 to SQLAgentOperatorRole, in order to view jobs and create own jobs.
    use msdb;
    go
    
    create user Test3 for login Test3;
    exec sp_addrolemember SQLAgentOperatorRole ,Test3
    


    Allen Li

    TechNet Community Support

    Tuesday, November 6, 2012 7:31 AM
  • You have made Test3 a member of the db_owner role.
    Login as Test3.
    EXEC sp_droprolemember db_denydatareader,Test3;
    They have elevated their privileges.

    Or, since you are a db_owner you can create a new database user. Let's say you know that one of the logins on the SQL Server is named Barry. You can make that login a user in Test:
    CREATE USER [Barry] FOR LOGIN [Barry];

    Since you are a db_owner you can grant access to a table such as T1 to Barry:
    GRANT SELECT ON OBJECT::T1 TO Barry;

    Since you are a db_owner you can impersonate Barry in the database:
    EXECUTE AS USER = 'Barry';
    SELECT * FROM T1;
    REVERT

    Again, elevation of privileges.

    So you can't really get away with making the person a db_owner, and then try to keep them from running wild in the database.

     

     


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

    Tuesday, November 6, 2012 4:44 PM
  • Check-out these built-in server roles for your administrator, http://msdn.microsoft.com/en-us/library/ms188659.aspx:

    1) dbcreator: for backup and restore permissions.

    2) serveradmin: for SQL Agent and Server configuration?

    3) diskadmin: for space resolution issues.

    4) dbcreator: for DDL and DML execution. CAUTION: If you don't want the DBA to have read/write access, then by definition you don't want them to have DML execution.

    5) 'Profiler and similar' activities also implies the need to have access to data, in readable form.

    In many cases, administrators are trusted to read data as a normal job function. If you really don't want administrators reading the data, then do you really want to be storing the data to begin with? Perhaps encrypting the data is a better choice than denying read permissions to your DBA.


    Wednesday, November 7, 2012 7:19 PM
  • I'm with Rick on this. Can't really restrict your DBA's permissions at the database level if they've got the db_owner role. It would be easier having your DBA give instructions/support over the phone to someone that isn't under such tight restrictions and has access to your databases.

    Your client needs to either accept a reduced level of support from your offshore DBA (diskadmin, serveradmin, ddladmin, alter database, db_ssisoperator,db_backupoperator, securityadmin [provided that your offshore DBA is not granted access to any of the databases], ) or bring in another DBA that isn't under such tight restrictions.

    Wednesday, November 7, 2012 7:54 PM
  • Hi Rick,

    Thanks for your reminder. I have modified my codes as following. Vivek1982s, please check whether the codes can achieve your requirements.

    -- Add user Test3 for login Test3 on database Test
    use Test;
    go
    
    create user Test3 for login Test3
    
    -- Add user Test3 to db_owner data base role, and deny reading and writing
    use Test;
    go
    
    exec sp_addrolemember db_backupoperator,Test3
    -- Please use Grant command to add other permission to the user.
    -- http://msdn.microsoft.com/en-us/library/ms187965.aspx
    
    -- Add login Test3 to dbcreator server role, in order to restore database
    use master;
    go
    
    exec sp_addsrvrolemember Test3,dbcreator 
    
    -- grant alter trace to login Test3, in order to use Profiler
    use master;
    go
    
    grant alter trace to Test3
    
    -- Add user Test3 to SQLAgentOperatorRole, in order to view jobs and create own jobs.
    use msdb;
    go
    
    create user Test3 for login Test3;
    exec sp_addrolemember SQLAgentOperatorRole ,Test3
    


    Allen Li

    TechNet Community Support

    Thursday, November 8, 2012 3:25 AM