Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 6 down vote favorite

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?

share|improve this question

2 Answers

up vote 3 down vote accepted

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

share|improve this answer
Thanks, it says in BOL: msdn.microsoft.com/en-us/library/ms188659.aspx?ppud=4 that securityadmin should be treated as sysadmin server role. Is there a way to narrow securityadmin rights to logins? Or will that "GRANT ALTER ANY LOGIN to AColleagu" do that? – jrara Aug 25 '11 at 13:13
@jrara: securityadmin is enough or the GRANT – gbn Aug 25 '11 at 13:20
2  
It should be treated as sysadmin because giving securityadmin rights allows the user to grant himself/herself sysadmin role permissions. – Shawn Melton Aug 25 '11 at 13:21
@jrara: in which case, you can't use securityadmin. In the db% roles, principals and permissions are separated. It's usual to not grant any server roles except maybe bulkadmin – gbn Aug 25 '11 at 13:37
up vote 3 down vote

Just so you have something to look forward to, in SQL Server Denali this will be even more flexible. Instead of granting individual rights one-by-one, you'll be able to define custom server roles, assign all those permissions to the role, then add members to the role. Some folks have blogged about it:

http://www.sqlsoldier.com/wp/sqlserver/customserverrolesindenali

http://www.straightpathsql.com/archives/2010/11/create-your-own-sql-server-server-roles/

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.