SQL Server – Permissions for Schema Compare and View Job History

jobsmaintenance-planspermissionssql serversql server 2014

I need to grant these permissions,
what would be the minimum level I could grant so that they can do their job?

I am looking at SQL Server Agent Fixed Database Roles
and also Database Engine Permission Basics

I still could not find exactly,
what would be the least permissions to be granted?

enter image description here

Best Answer

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.

Related Question