Document Details

  --- Create a login to run the assessment
  use master;
  -- NOTE: SQL instances that host replicas of Always On availability groups must use the same SID for the SQL login.
    -- After the account is created in one of the members, copy the SID output from the script and include this value
    -- when executing against the remaining replicas.
    -- When the SID needs to be specified, add the value to the @SID variable definition below.
  DECLARE @SID NVARCHAR(MAX) = N'';
  IF (@SID = N'')
  BEGIN
    CREATE LOGIN [evaluator]
        WITH PASSWORD = '<provide a strong password>'
  END
  ELSE
  BEGIN
    DECLARE @SQLString NVARCHAR(500) = 'CREATE LOGIN [evaluator]
      WITH PASSWORD = ''<provide a strong password>''
      , SID = ' + @SID
    EXEC SP_EXECUTESQL @SQLString
  END
  SELECT @SID = N'0x'+CONVERT(NVARCHAR(100), sid, 2) FROM sys.syslogins where name = 'evaluator'
  IF (ISNULL(@SID,'') != '')
    PRINT N'Created login [evaluator] with SID = '''+ @SID +'''. If this instance hosts any Always On Availability Group replica, use this SID value when executing the script against the instances hosting the other replicas'
  ELSE
    PRINT N'Login creation failed'
  GO
  
  -- Create user in every database other than tempdb, model, and secondary AG databases (with connection_type = ALL) and provide minimal read-only permissions.
  USE master;
  EXECUTE sp_MSforeachdb '
    USE [?];
    IF (''?'' NOT IN (''tempdb'',''model''))
    BEGIN
      DECLARE @is_secondary_replica BIT = 0;
      IF CAST(PARSENAME(CAST(SERVERPROPERTY(''ProductVersion'') AS VARCHAR), 4) AS INT) >= 11
      BEGIN
        DECLARE @innersql NVARCHAR(MAX);
        SET @innersql = N''
          SELECT @is_secondary_replica = IIF(
            EXISTS (
              SELECT 1
              FROM sys.availability_replicas a
              INNER JOIN sys.dm_hadr_database_replica_states b
                ON a.replica_id = b.replica_id
              WHERE b.is_local = 1
                AND b.is_primary_replica = 0
                AND a.secondary_role_allow_connections = 2
                AND b.database_id = DB_ID()
            ), 1, 0
          );
        '';
        EXEC sp_executesql @innersql, N''@is_secondary_replica BIT OUTPUT'', @is_secondary_replica OUTPUT;
      END

      IF (@is_secondary_replica = 0)
      BEGIN
          CREATE USER [evaluator] FOR LOGIN [evaluator];
          GRANT SELECT ON sys.sql_expression_dependencies TO [evaluator];
          GRANT VIEW DATABASE STATE TO [evaluator];
      END
    END'
  GO
  
  -- Provide server level read-only permissions
  USE master;
  GRANT SELECT ON sys.sql_expression_dependencies TO [evaluator];
  GRANT EXECUTE ON OBJECT::sys.xp_regenumkeys TO [evaluator];
  GRANT EXECUTE ON OBJECT::sys.xp_instance_regread TO [evaluator];
  GRANT VIEW DATABASE STATE TO [evaluator];
  GRANT VIEW SERVER STATE TO [evaluator];
  GRANT VIEW ANY DEFINITION TO [evaluator];
  GO
  
  -- Provide msdb specific permissions
  USE msdb;
  GRANT EXECUTE ON [msdb].[dbo].[agent_datetime] TO [evaluator];
  GRANT SELECT ON [msdb].[dbo].[sysjobsteps] TO [evaluator];
  GRANT SELECT ON [msdb].[dbo].[syssubsystems] TO [evaluator];
  GRANT SELECT ON [msdb].[dbo].[sysjobhistory] TO [evaluator];
  GRANT SELECT ON [msdb].[dbo].[syscategories] TO [evaluator];
  GRANT SELECT ON [msdb].[dbo].[sysjobs] TO [evaluator];
  GRANT SELECT ON [msdb].[dbo].[sysmaintplan_plans] TO [evaluator];
  GRANT SELECT ON [msdb].[dbo].[syscollector_collection_sets] TO [evaluator];
  GRANT SELECT ON [msdb].[dbo].[sysmail_profile] TO [evaluator];
  GRANT SELECT ON [msdb].[dbo].[sysmail_profileaccount] TO [evaluator];
  GRANT SELECT ON [msdb].[dbo].[sysmail_account] TO [evaluator];
  GO
  
  -- Clean up
  --use master;
  -- EXECUTE sp_MSforeachdb 'USE [?]; BEGIN TRY DROP USER [evaluator] END TRY BEGIN CATCH PRINT ERROR_MESSAGE() END CATCH;'
  -- BEGIN TRY DROP LOGIN [evaluator] END TRY BEGIN CATCH PRINT ERROR_MESSAGE() END CATCH;
  --GO
   

  -- Create a login to run the assessment
  use master;
  DECLARE @SID NVARCHAR(MAX) = N'';
  CREATE LOGIN [MYDOMAIN\MYACCOUNT] FROM WINDOWS;
  SELECT @SID = N'0x'+CONVERT(NVARCHAR, sid, 2) FROM sys.syslogins where name = 'MYDOMAIN\MYACCOUNT'
  IF (ISNULL(@SID,'') != '')
    PRINT N'Created login [MYDOMAIN\MYACCOUNT] with SID = ' + @SID
  ELSE
    PRINT N'Login creation failed'
  GO    

  -- Create user in every database other than tempdb, model, and secondary AG databases (with connection_type = ALL) and provide minimal read-only permissions.
  USE master;
  EXECUTE sp_MSforeachdb '
    USE [?];
    IF (''?'' NOT IN (''tempdb'',''model''))
    BEGIN
      DECLARE @is_secondary_replica BIT = 0;
      IF CAST(PARSENAME(CAST(SERVERPROPERTY(''ProductVersion'') AS VARCHAR), 4) AS INT) >= 11
      BEGIN
        DECLARE @innersql NVARCHAR(MAX);
        SET @innersql = N''
          SELECT @is_secondary_replica = IIF(
            EXISTS (
                SELECT 1
                FROM sys.availability_replicas a
                INNER JOIN sys.dm_hadr_database_replica_states b
                ON a.replica_id = b.replica_id
                WHERE b.is_local = 1
                AND b.is_primary_replica = 0
                AND a.secondary_role_allow_connections = 2
                AND b.database_id = DB_ID()
            ), 1, 0
          );
        '';
        EXEC sp_executesql @innersql, N''@is_secondary_replica BIT OUTPUT'', @is_secondary_replica OUTPUT;
      END
      IF (@is_secondary_replica = 0)
      BEGIN
        CREATE USER [MYDOMAIN\MYACCOUNT] FOR LOGIN [MYDOMAIN\MYACCOUNT];
        GRANT SELECT ON sys.sql_expression_dependencies TO [MYDOMAIN\MYACCOUNT];
        GRANT VIEW DATABASE STATE TO [MYDOMAIN\MYACCOUNT];
      END
    END'
  GO

  -- Provide server level read-only permissions
  use master;
  GRANT SELECT ON sys.sql_expression_dependencies TO [MYDOMAIN\MYACCOUNT];
  GRANT EXECUTE ON OBJECT::sys.xp_regenumkeys TO [MYDOMAIN\MYACCOUNT];
  GRANT EXECUTE ON OBJECT::sys.xp_instance_regread TO [MYDOMAIN\MYACCOUNT];
  GRANT VIEW DATABASE STATE TO [MYDOMAIN\MYACCOUNT];
  GRANT VIEW SERVER STATE TO [MYDOMAIN\MYACCOUNT];
  GRANT VIEW ANY DEFINITION TO [MYDOMAIN\MYACCOUNT];
  GO

  -- Provide msdb specific permissions
  use msdb;
  GRANT EXECUTE ON [msdb].[dbo].[agent_datetime] TO [MYDOMAIN\MYACCOUNT];
  GRANT SELECT ON [msdb].[dbo].[sysjobsteps] TO [MYDOMAIN\MYACCOUNT];
  GRANT SELECT ON [msdb].[dbo].[syssubsystems] TO [MYDOMAIN\MYACCOUNT];
  GRANT SELECT ON [msdb].[dbo].[sysjobhistory] TO [MYDOMAIN\MYACCOUNT];
  GRANT SELECT ON [msdb].[dbo].[syscategories] TO [MYDOMAIN\MYACCOUNT];
  GRANT SELECT ON [msdb].[dbo].[sysjobs] TO [MYDOMAIN\MYACCOUNT];
  GRANT SELECT ON [msdb].[dbo].[sysmaintplan_plans] TO [MYDOMAIN\MYACCOUNT];
  GRANT SELECT ON [msdb].[dbo].[syscollector_collection_sets] TO [MYDOMAIN\MYACCOUNT];
  GRANT SELECT ON [msdb].[dbo].[sysmail_profile] TO [MYDOMAIN\MYACCOUNT];
  GRANT SELECT ON [msdb].[dbo].[sysmail_profileaccount] TO [MYDOMAIN\MYACCOUNT];
  GRANT SELECT ON [msdb].[dbo].[sysmail_account] TO [MYDOMAIN\MYACCOUNT];
  GO
  
  -- Clean up
  --use master;
  -- EXECUTE sp_MSforeachdb 'USE [?]; DROP USER [MYDOMAIN\MYACCOUNT]'
  -- DROP LOGIN [MYDOMAIN\MYACCOUNT];
  --GO