locked
SQL Admin without having local Admin rights RRS feed

  • Question

  • Hello,

    My DBA's are insisting on having local admin rights on the SQL servers to be able to perform their daily BAU work, however I'm resisting this request on security grounds.

    Question: Is there a way to provide all the required SQL access rights via the SQL Server Management Studio 2008 r2 installed on their admin PC's, without having local admin rights on the actual remote SLK servers? If so, can you please supply detail or a URL.

    Thanks,

    Cosmo

    Monday, August 29, 2011 7:54 AM

Answers

  • Cosmo,

    This is a normal struggle pretty much everywhere. I'm not certain what daily BAU work is, but if they need access to the servers you are right to ask why and to what. Basically *almost* everything (ok, if you know SQL Server, everything) is possible to do through SSMS at varying difficulties.

    Most items can be mitigated through normal A/D rights and ACLs. For example a backup needs to be transferred between dev and prod. Access can be given to the specific backup (or tape libraries) areas so that this can be completed.

    Other questions aren't so easy. Who is going to run the patches? Will the DBA script out patching to have it pushed out via some type of corporate software? Will it be done by hand? etc.

    Also, in event of an issue... say a server crashes and SQL Server is hosed (actualy binaries not databases), do they have an account to have access to get the install completed and the databases restored?

    My opinion as to whether the DBAs NEED local admin is that they don't. However they DO need to have their permissions set correctly so that they can access the resources that they need on that server. If I can't pull log files I'd pester my WinAdmins team to send me the files, which takes away time for them to be doing other tasks and not mine.

    -Sean

    Monday, August 29, 2011 1:21 PM
    Answerer
  • SYSAdmin in their sql server login should be fine for adminstrating the sql server database engine. It has all the admin rights one can think of. Even they access command prompt with that access using xp_cmdshell procedure.

     

    Pls mark as answer, if this helps.


    - Kerobin
    Friday, September 2, 2011 1:01 PM

All replies

  • Cosmo,

    This is a normal struggle pretty much everywhere. I'm not certain what daily BAU work is, but if they need access to the servers you are right to ask why and to what. Basically *almost* everything (ok, if you know SQL Server, everything) is possible to do through SSMS at varying difficulties.

    Most items can be mitigated through normal A/D rights and ACLs. For example a backup needs to be transferred between dev and prod. Access can be given to the specific backup (or tape libraries) areas so that this can be completed.

    Other questions aren't so easy. Who is going to run the patches? Will the DBA script out patching to have it pushed out via some type of corporate software? Will it be done by hand? etc.

    Also, in event of an issue... say a server crashes and SQL Server is hosed (actualy binaries not databases), do they have an account to have access to get the install completed and the databases restored?

    My opinion as to whether the DBAs NEED local admin is that they don't. However they DO need to have their permissions set correctly so that they can access the resources that they need on that server. If I can't pull log files I'd pester my WinAdmins team to send me the files, which takes away time for them to be doing other tasks and not mine.

    -Sean

    Monday, August 29, 2011 1:21 PM
    Answerer
  • Thanks for such a prompt and informative response  :-)

    Does anyone else have any suggestions?

    Monday, August 29, 2011 9:33 PM
  • Thanks for such a prompt and informative response  :-)

    Does anyone else have any suggestions?

    Monday, August 29, 2011 9:33 PM
  • SYSAdmin in their sql server login should be fine for adminstrating the sql server database engine. It has all the admin rights one can think of. Even they access command prompt with that access using xp_cmdshell procedure.

     

    Pls mark as answer, if this helps.


    - Kerobin
    Friday, September 2, 2011 1:01 PM