Sql-server – SQL Server: how to avoid sysadmin rights but give adequate rights

Securitysql server

I have a collegue who want to get an access to one SQL Server 2008 instance. I'll have to give him rights to this instance. He should have rights to do is e.g.

  • Add and modify server logins
  • Add and modify maintenanace plans (e.g. create backups from the databases)
  • Schedule Agent jobs

I don't want to give him sysadmin rights, what rights should be given?

Best Answer

For server logins, you can grant "securityadmin". The "newer" way is to run 

GRANT ALTER ANY LOGIN TO AColleague

Edit: Securityadmin allows someone to bootstrap themselves to sysadmin. Not good. Don't know how to workaround this at the server level

For jobs, look at the "SQL Server Agent Fixed Database Roles"

For maintenance plans, it looks like "sysadmin" only

Related Question