Below are the required SQL Server level permissions needed for proper Brain Box SQL functionality.

* Permissions below start with the least privilege and elevates thereafter

  1. Minimum permissions for server and database details:

    GRANT VIEW SERVER STATE TO [login name];
    GRANT VIEW ANY DEFINITION TO [login name];

    For SQL Server 2014 and above grant server permission
    GRANT CONNECT ANY DATABASE TO [login name];

    For SQL Server 2012 and below grant each database access
    CREATE USER [login name] FOR LOGIN [login name];

  2. To view job details

    GRANT EXEC ON MASTER.dbo.XP_SQLAGENT_ENUM_JOBS TO [login name];
    USE MSDB; EXEC SP_ADDROLEMEMBER N’db_datareader’, N'[login name]’;

  3. To start stop/stop jobs
    ** warning: highly elevated permission. proceed at own discretion **

    USE MSDB;EXEC SP_ADDROLEMEMBER N’SQLAgentOperatorRole’, N’login name’;

Leave a Reply