locked
virtual sysadmin RRS feed

  • Question

  • I'd like to give a group sysadmin-like rights on a server but restrict access to certain databases.

    specifically i want the group to be able to run jobs, replication, ssis but stop write access to certain databases

    anyone out there have a script that'll do it for me?

    thanks
    Wednesday, October 28, 2009 3:46 AM

Answers

  • There is no such pre-defined scripts or groups , but we can try to achieve this around .

    I'd like to give a group sysadmin-like rights on a server but restrict access to certain databases.

    -- For this the best option would be to add them in to DB_OWNER role only for those db which you would like to give access.

    specifically i want the group to be able to run jobs, replication, ssis but stop write access to certain databases

     --  For this , you must explicitly add the users to msdb database under the public role and also give them

    USE MSDB
    GO
    GRANT EXECUTE ON sp_start_job to USER

    Thanks, Leks
    • Marked as answer by GT OZ Wednesday, October 28, 2009 10:41 PM
    Wednesday, October 28, 2009 5:10 AM