To be fair it’s not actually hidden, just not so well known.

Have you ever wondered how to grant permissions to create/modify jobs? How about SSIS packages?

Every database has the following built in roles:

• db_accessadmin
• db_backupoperator
• db_datareader
• db_datawriter
• db_ddladmin
• db_denydatareader
• db_denydatawriter
• db_owner
• db_securityadmin

However the msdb database has a number of special purpose built in roles:

• SSIS Permissions
  • db_ssisadmin
  • db_ssisltduser
  • db_ssisoperator
• Data Collector Permissions
  • dc_admin
  • dc_operator
  • dc_proxy
• Policy Based Management Permissions
  • PolicyAdministratorRole
• Server Group Permissions
  • ServerGroupAdministratorRole
  • ServerGroupReaderRole
• SQL Server Agent Permissions
  • SQLAgentOperatorRole
  • SQLAgentReaderRole
  • SQLAgentUserRole
• Multi Server Jobs
  • TargetServersRole
• Database Mail Permissions
  • DatabaseMailUserRole
• Server Utility Permissions
  • UtilityCMRReader
  • UtilityIMRReader
  • UtilityIMRWriter

 

The important part here is not to know all of the roles and what each does. The important part is to know that they exist. That way next time you think “Hmm, I wonder how I grant permissions for ….” you will now that msdb is a good place to start looking.

Filed under: Microsoft SQL Server, Security, SQLServerPedia Syndication Tagged: microsoft sql server, roles, security