none
SSMS on App Server

    Question

  • AppServer and SQL server in place.

    Is it common to install SSMS on Application Server to which a vendor will have full access and will be db_owner on SQL server?

    Personally, I don't see a problem. Anyway his AD user account is member of local Admin group on Application server so he can install it if needed.

    I am asking because just prepared new App server and created a DB with db_owner role for that user for upcoming project.

    Thanks.


    --- When you hit a wrong note its the next note that makes it good or bad. --- Miles Davis

    Tuesday, February 14, 2017 1:15 PM

Answers

  • As long as you're aware of, and ok with, the performance and security implications of installing and using SSMS on app server, there should be no problem with doing so. The bigger question is, why is that neccessary? Doesn't the vendor have SSMS installed on his own laptop or workstation connectable to the organization network? 

    SSMS does consume a certain amount of memory & other system resources when launched and used, so this could cause resource contention with the application itself. db_owner privilege can drop, destroy or otherwise compromise data owned by your organization, so that's a trust issue.

    I would probably be more concerned about the vendor running SELECT * queries that could add significant workload and resource contention on the db server, especially if it hosts other databases besides his. But hopefully he knows better than to do such a thing. Usually, you don't want users (vendor or otherwise) running adhoc queries directly against a database once it's in production. Typically, you want to restrict workload to application processes only, unless their is break-fix work that needs to be done.

    HTH,


    Phil Streiff, MCDBA, MCITP, MCSA

    • Edited by philfactor Tuesday, February 14, 2017 1:32 PM
    • Marked as answer by pob579 Tuesday, February 14, 2017 1:55 PM
    Tuesday, February 14, 2017 1:24 PM

All replies

  • As long as you're aware of, and ok with, the performance and security implications of installing and using SSMS on app server, there should be no problem with doing so. The bigger question is, why is that neccessary? Doesn't the vendor have SSMS installed on his own laptop or workstation connectable to the organization network? 

    SSMS does consume a certain amount of memory & other system resources when launched and used, so this could cause resource contention with the application itself. db_owner privilege can drop, destroy or otherwise compromise data owned by your organization, so that's a trust issue.

    I would probably be more concerned about the vendor running SELECT * queries that could add significant workload and resource contention on the db server, especially if it hosts other databases besides his. But hopefully he knows better than to do such a thing. Usually, you don't want users (vendor or otherwise) running adhoc queries directly against a database once it's in production. Typically, you want to restrict workload to application processes only, unless their is break-fix work that needs to be done.

    HTH,


    Phil Streiff, MCDBA, MCITP, MCSA

    • Edited by philfactor Tuesday, February 14, 2017 1:32 PM
    • Marked as answer by pob579 Tuesday, February 14, 2017 1:55 PM
    Tuesday, February 14, 2017 1:24 PM
  • This is a new SQL server with first DB installed.

    The vendor told that his app will require db_owner, so users will not access db.

    The vendor is responsible for the APP functionality (and associated DB). Backups will be scheduled as necessary.

    So the trust question is not an issue here.

    I appreciate your detailed explanation and will not install SSMS. Can do it if the vendor will prove the necessity.

    Don't think so...


    --- When you hit a wrong note its the next note that makes it good or bad. --- Miles Davis

    Tuesday, February 14, 2017 1:52 PM