db roles / server roles

[itvixen]

Hi,

I'm looking for some guidance/help regarding setting up a sa - lite account in SQL 2005. I need to give another admin rights to create/monitor maintenance plans, backup and restore databases, monitor performance/logins, but NOT be able to have any rights on several tables (and of course not being able to set user permissions).

I've tried using server and db roles but haven't been able to determine how to give someone w/o full sa rights access to maintenance plans.

If you can think of soemthing, please let m eknow.

Jenn

[skhanal]

Many of the rights you want are sysadmin functions.

Did you try granting higher privilege and DENYing the select and other rights?.

[rmiao]

You can't stop people once put them in sysadmin role.

[itvixen]

Thank you both for your replies

Rmiao,

r u saying that it's not possible to give a user backup/restore rights and maintenance plan rights - but disallow them access to certain tables and creating/editing users?

Thanks so much!

[rmiao]

You need sysadmin rights to create maintenance plan, how can you stop sysadmin from access table?