determining is sysadmin is appropriate

Question

Have you got any suggestions on some useful questions that could help challenge whether the more powerful server level roles such as sysadmin privileges are necessary for certain support staff in a SQL Server instance? We must perform an security health check and sign-off whether users granted elevated server level roles in MSSQL are necessary appropriate, but we need some helpful criteria to help differentiate those that really need sysadmin permissions, to those that probably don't. In one common scenario seen before, we have found is dedicated DBA’s (obviously) have sysadmin privileges, but then so do more generic/non DBA IT support staff which is clearly not ideal (some for data analysts which again seems excessive). I am trying to find a way through challenging questions, to differentiate users whereby it is essential to have sysadmin permissions, or where there may be a more suitable lower risk role that should be assigned to individuals. 

Also secondly, is there a collective term you use in your company for all the SQL Servers under your support remit? e.g. 'estate', 'fleet', 'inventory' or other? 

Answer 1

Hi @crib bar

I found no relevant information. Different administrators may have different criteria for granting user permissions. Perhaps you can limit and control user access by setting up server roles: https://learn.microsoft.com/en-us/sql/relational-databases/security/authentication-access/server-level-roles?view=sql-server-ver16.

Best regards,

Aniya

Answer 2

The sysadmin role is a powerful role that has administrative control over the entire SQL Server instance. Members of this role can perform any action on the SQL Server, including altering server settings, creating, modifying, or deleting databases, and managing security. So you need to determine who or what staff that falls under this criteria. Also important to be cautious about who you assign this role to, as it grants significant privileges that can impact the security and stability of your SQL Server environment. In your case, probably a senior IT support staff or System Administrator and possibly SQL Server service accounts may require a sysadmin role.

To your second question, you can collectively call your SQL servers "a cluster of servers". There isn't a specific name for it, just depends on context and level of technical detail you want to convey.

Hope this helps