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 specifically can't they do right now? Pretty much every permission is granular in SQL Server so I'd need to understand the specific requirements. – George.Palacios Apr 25 '18 at 14:37
  • It seems like you want to give out permissions at the server level that only pertain to database level settings? I'm confused why dbo on each database isn't an option. – LowlyDBA Apr 25 '18 at 14:38
  • @George.Palacios Currently, they have SA privileges. My team lead wants to cut their access back just below sa privileges, but has enough access to where they can perform their on-call duties such as promoting stored procs from QA to PROD, one time back ups, running ad-hoc queries, and their day-to-day business. He does not want them to be able to change server level settings such has our min/max memory configurations. Basically, he wants all sa requests to come through us as we have a couple rouge dba dev's that are changing configurations whenever they choose. – Jeff Stebbens Apr 25 '18 at 14:51
  • I've implemented something similar here for our BI team with a CREATE DATABASE trigger which adds an AD group to the db_owner role, and given that AD group the ability to create databases (Groups can't own a database) – George.Palacios Apr 25 '18 at 14:58
  • But yeah effectively it sounds like you'll need database level permissions. – George.Palacios Apr 25 '18 at 14:59

what about:

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

then you wait for someone's complaining and give also what they need.


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 = 
    use ? 
    if DB_NAME() not in (''master'',''tempdb'',''model'',''msdb'',''SSISDB'') 

EXEC sp_msforeachdb @command1 = 
    use ? 
    if DB_NAME() not in (''master'',''tempdb'',''model'',''msdb'',''SSISDB'') 
    ALTER ROLE [db_owner] ADD MEMBER [<DOMAIN\MyGroup>]
  • I think this will have to be the way we end up going. I know my team lead doesn't want to do it this way as we have over 800 databases and giving db_owner to everything will be a lot of overhead. But with my testing and the answers I've gotten; it's confirmed my testing. He just can't have his cake and eat it too. Thanks for the response! – Jeff Stebbens Apr 25 '18 at 15:07
  • 1
    This dbatools command might be useful for that. – LowlyDBA Apr 25 '18 at 15:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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