For the msdb SQL Server Agent roles, granting SQLAgentUserRole
allows them the ability to manage jobs which only they own, and see the job history of those jobs too.
To compare schema on all table of any DB, assuming you want to also allow them to have SELECT
to all tables, the db_datareader
role should be fine per DB.
Otherwise, if you want to be specific, then allow explicit VIEW DEFINITION
on the schema per DB which you want to all them to compare and SELECT
to the tables you want them to be able to compare the data.
EXPLICIT EXAMPLES TSQL (per the article)
GRANT VIEW DEFINITION ON OBJECT::Region TO Ted
GRANT SELECT ON OBJECT::Region TO Ted
EXPLICIT EXAMPLES TSQL (per the request in your screen shot)
GRANT VIEW ANY DEFINITION TO User
USE msdb
EXECUTE sp_addrolemember @rolename = 'SQLAgentReaderRole'
,@membername = 'username'
I think it depends on how well you want to lock it down really, and how trustworthy they are for what you are allowing them to have access to.