Login permissions outside of sa - SQL Server 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.
what about:


removing them from the sa server role
give them the following server roles: dbcreator, securityadmin
give them db_owner on every user database they need
If they need to use SQL Agent, on msdb give them
SQLAgentOperatorRole
If they deploy SSIS packages, on SSISDB, give them ssis_admin


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.

EXEC sp_msforeachdb @command1 = 
N'
    use ? 
    if DB_NAME() not in (''master'',''tempdb'',''model'',''msdb'',''SSISDB'') 
    CREATE USER [&lt;DOMAIN\MyGroup&gt;] FOR LOGIN [&lt;DOMAIN\MyGroup&gt;]
' 

EXEC sp_msforeachdb @command1 = 
N'
    use ? 
    if DB_NAME() not in (''master'',''tempdb'',''model'',''msdb'',''SSISDB'') 
    ALTER ROLE [db_owner] ADD MEMBER [&lt;DOMAIN\MyGroup&gt;]
'