0

I would like your help designing a custom server role (Or understanding if what I'm after is possible).

I'm sure this isn't a unique problem out there - I head up a data and analytics team, but our DBA sits in IT and is not a dedicated (or trained) DBA. We often have to wait several days for different team members to be granted the right access to DB's etc and it massively delays us. Understandably, IT won't give me sysadmin access, which is fair enough.

So I'd like to offer a custom server role for them to apply to all of my team's servers that enables me to:

  • Access every database
  • Add users to roles (such as dbreader, dbwriter)
  • Add, alter, drop databases
  • View all object defintions and DMO's for performance

Questions:

  1. Is this possible?
  2. Can it be achieved in a script? (it would be a lot easier if I could just give them a script to run)

Thanks in advance for any help you can give me!

zx485
  • 28,498
  • 28
  • 50
  • 59
Sanchez333
  • 318
  • 3
  • 11
  • so you want to be added to the sysadmin role basically... secadmin would allow you to add users, create logons, etc... and db_owner could make the DDL changes you mention, – S3S Jul 25 '18 at 15:54
  • This sounds like you want every database [Fixed-Database Role](https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/database-level-roles?view=sql-server-2017#fixed-database-roles) that grants permissions, apart from `db_owner`. `db_accessadmin` to create users, `db_securityadmin` to alter roles, `db_ddladmin` to change definitions, etc, as well as a few of the [Fixed Server-Level Roles](https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/server-level-roles?view=sql-server-2017#fixed-server-level-roles). That's a lot. – Thom A Jul 25 '18 at 16:01
  • Thanks very much guys. I appreciate it's a big ask. If I only wanted to add existing users to roles, would that lower the access I needed? – Sanchez333 Jul 26 '18 at 07:27

0 Answers0