Where I'm at, our DBA team is split up into admins, BI, DEV. We are wanting to have our admins have SA rights and the other two teams have everything but sa and to take away the ability to change server settings (min/max memory, file locations, etc…)
I have played around with different server-level permission combos, but they force the need to give individual rights on the database(s) and we are looking to avoid this since these groups are still in our DBA on-call rotation.
I also found the Control Server securable. While this does everything I want it to, it also gives the ability to change server settings (Alter Resources). This would be the perfect answer if only it didn't give the ability to change server side settings. I have tried running a DENY on Alter Resources, but Control Server trumps this DENY.
Is there a way to do what I'm needing in SQL Server that I haven't thought of?
I believe I have tested this thoroughly, but wanted to ask the community before I go back to my team lead. I hope I've given enough information, but let me know if I need more.
Best Answer
what about:
then you wait for someone's complaining and give also what they need.
Plus:
Well, I know that this is not the best script (10 min...lol), but will solve your problem to give permissions on every database. Maybe you can improve it to solve your problem.