Updated 2016-02-09: While tracing a server today I saw some queries being sent from SCOM accessing a table which is not documented below. I've written a note about it on the TechNet forums but you may want to grant access to msdb.dbo.syspolicy_policy_execution_history_details_internal in addition to what I've listed below until it's fixed.

The Microsoft System Center Management Pack for SQL Server documentation describes the minimum permissions required for SCOM to do its monitoring (unless you just grant it sysadmin, which would be a terrible idea so please don't do it).

I wrote a procedure which will go through and set all of that up or the pieces that are missing. This needs to be evaluated each night because new databases need changes in order to come up on monitoring and it will also hammer the logs with errors until it gets what it wants. You could ensure this with a scheduled agent job, a policy, or however else you like.

Use master -- Preferably elsewhere
Go

If Not Exists (Select Top 1 0 From sys.procedures p Join sys.schemas s On p.schema_id = s.schema_id Where s.name = 'dbo' And p.name = 'Create_Or_Update_Scom_Login_Security')
Begin
	Exec sp_executesql N'Create Procedure dbo.Create_Or_Update_Scom_Login_Security
As
Begin
	Select 1 As Dummy
End
'
End
Go

Alter Procedure dbo.Create_Or_Update_Scom_Login_Security
As
Begin
	Set Nocount On

	Declare	@ScomLogin Sysname
	Declare	@SqlCommand Nvarchar(Max)

	If	Default_Domain() = '' -- Enter your domain name here, this is so you can have multiple domains with different SCOM logins
	Begin
		Set	@ScomLogin = '' -- Enter your SCOM login here, as Domain\Username
	End
	Else
	Begin
		Raiserror('Default Domain was not mapped to a SCOM login name.', 16, 1)
		Return
	End

	If Not Exists (Select Top 1 0 From master.sys.server_principals Where name = @ScomLogin)
	Begin
		Print 'Creating SCOM login'
		Set	@SqlCommand = 'Create Login ' + Quotename(@ScomLogin) + ' From Windows'
		Exec sp_executesql @SqlCommand
	End

	If Not Exists (Select Top 1 0 From master.sys.server_permissions Join master.sys.server_principals On grantee_principal_id = principal_id Where name = @ScomLogin And permission_name = 'VIEW ANY DEFINITION' And state_desc = 'GRANT')
	Begin
		Print 'Granting SCOM login permission to View Any Definition'
		Set	@SqlCommand = 'Grant View Any Definition To ' + Quotename(@ScomLogin)
		Exec sp_executesql @SqlCommand
	End

	If Not Exists (Select Top 1 0 From master.sys.server_permissions Join master.sys.server_principals On grantee_principal_id = principal_id Where name = @ScomLogin And permission_name = 'VIEW SERVER STATE' And state_desc = 'GRANT')
	Begin
		Print 'Granting SCOM login permission to View Server State'
		Set	@SqlCommand = 'Grant View Server State To ' + Quotename(@ScomLogin)
		Exec sp_executesql @SqlCommand
	End

	If Not Exists (Select Top 1 0 From master.sys.server_permissions Join master.sys.server_principals On grantee_principal_id = principal_id Where name = @ScomLogin And permission_name = 'VIEW ANY DATABASE' And state_desc = 'GRANT')
	Begin
		Print 'Granting SCOM login permission to View Any Database'
		Set	@SqlCommand = 'Grant View Any Database To ' + Quotename(@ScomLogin)
		Exec sp_executesql @SqlCommand
	End

	If Not Exists (Select Top 1 0 From master.sys.database_principals dp Join master.sys.database_permissions dpe On dp.principal_id = dpe.grantee_principal_id Join master.sys.system_views sv On dpe.major_id = sv.object_id Join master.sys.schemas s On sv.schema_id = s.schema_id Where dpe.class_desc = 'OBJECT_OR_COLUMN' And dpe.permission_name = 'SELECT' And dpe.state_desc = 'GRANT' And dp.name = @ScomLogin And s.name = 'sys' And sv.name = 'database_mirroring_witnesses')
	Begin
		-- This is in the scripts provided in the management pack but not mentioned elsewhere, it does appear to be used though
		Print 'Granting SCOM login Select on master.sys.database_mirroring_witnesses'
		Set	@SqlCommand = 'Grant Select On master.sys.database_mirroring_witnesses To ' + Quotename(@ScomLogin)
		Exec sp_executesql @SqlCommand
	End

		Declare @Databases Table (
			name sysname
		)

		-- Databases for 2005+
		Declare	@GetDatabasesQuery nvarchar(max) 
		Set @GetDatabasesQuery = 'Select	Quotename(name) As name
From	master.sys.databases sd
Left Join master.sys.database_mirroring dm
On		sd.database_id = dm.database_id 
Where	state_desc = ''ONLINE''
And		is_read_only = 0 
And		Isnull(dm.mirroring_role, 0) = 0
'

		-- Exclude availability groups for 2012+ 
		If		Cast(Serverproperty('ProductVersion') As Nvarchar(20)) Like '1%'
		And		Cast(Serverproperty('ProductVersion') As Nvarchar(20)) Not Like '10%'
		Begin
			Set		@GetDatabasesQuery = @GetDatabasesQuery + 'And		Not Exists (
	Select	Top 1 0 
	From	sys.databases d
	Join	sys.dm_hadr_availability_replica_states hars
	On		d.replica_id = hars.replica_id
	And		role_desc = ''SECONDARY''
	Where	d.database_id = sd.database_id)
'
		End

		Insert	@Databases
		Exec	sp_executesql @GetDatabasesQuery

		-- Create a corresponding user in each database where required and where possible
		Declare @DatabaseName Sysname

		Declare AddScom Cursor Local Forward_Only Static Read_Only For Select name From @Databases Order By name
		Open AddScom
		Fetch Next From AddScom Into @DatabaseName
		While @@fetch_status = 0
		Begin
			Set @SqlCommand = 'Use ' + @DatabaseName + '
Begin Try
	If Not Exists (Select Top 1 0 From sys.sysusers Where name = ''' + @ScomLogin + ''')
	Begin 
		Print ''Creating user for SCOM login in ' + @DatabaseName + '''; 
		Create User ' + Quotename(@ScomLogin) + '
	End 
End Try 
Begin Catch
	-- Errors here are usually non-fatal
End Catch'
			Exec sp_executesql @SqlCommand
			Fetch Next From AddScom Into @DatabaseName
		End

		Close AddScom
		Deallocate AddScom

		-- Add required database role memberships
		If Not Exists (Select Top 1 0 From msdb.sys.database_principals dp Join msdb.sys.database_role_members drm On dp.principal_id = drm.member_principal_id Join msdb.sys.database_principals dpr On drm.role_principal_id = dpr.principal_id Where dp.name = @ScomLogin And dpr.name = 'SQLAgentReaderRole')
		Begin
			Print 'Adding user for SCOM login to SQLAgentReaderRole'
			Exec msdb.dbo.sp_addrolemember 'SQLAgentReaderRole', @ScomLogin
		End

		-- This database role only exists on 2008+ 
		If Exists (Select Top 1 0 From msdb.sys.sysusers Where name = 'PolicyAdministratorRole' And issqlrole = 1)
		And Not Exists (Select Top 1 0 From msdb.sys.database_principals dp Join msdb.sys.database_role_members drm On dp.principal_id = drm.member_principal_id Join msdb.sys.database_principals dpr On drm.role_principal_id = dpr.principal_id Where dp.name = @ScomLogin And dpr.name = 'PolicyAdministratorRole')
		Begin
			Print 'Adding user for SCOM login to PolicyAdministratorRole'
			Exec msdb.dbo.sp_addrolemember 'PolicyAdministratorRole', @ScomLogin
		End

		-- Remove the sysadmin membership
		If Exists (Select Top 1 0 From master.sys.server_principals sp Join master.sys.server_role_members srp On sp.principal_id = srp.member_principal_id Join master.sys.server_principals spr On srp.role_principal_id = spr.principal_id Where sp.name = @ScomLogin And spr.name = 'sysadmin')
		Begin
			Print 'Removing sysadmin server role membership from SCOM login' -- This is often how it's set up initially
			Exec sp_dropsrvrolemember @ScomLogin, 'sysadmin'
		End
End
Go

-- Exec dbo.Create_Or_Update_Scom_Login_Security

I was