SCOM configuration script for low-privilege environments

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