Architecture v/s Code - Validating sys admins in SQL Server

In the misused agile world, it is always difficult to make sure the code is following the Architecture or Architecture document is following the code. 'Misused agile' means, using the word agile to make developers work all day and night for frequent releases. They struggle to meet the releases and there are high chances for taking deviations, shortcuts or hacks which eventually ends up Architecture and code going in 2 separate directions.

One if the best way to ensure they are inline by using Simon Brown;s C4 architecture model. C4 clearly document the structure of the code. One area which that model is not exactly covering as is the security model of the execution. Architects has to review the deployment document before any deployment or at least after deployment to ensure the security model is right.

With no further introduction let us take a problem of service accounts having higher permissions than needed. Either as Architects we can review each and every DB Server Instance and raise alarms or defects. Or make it automated.

Below script gives the list of logins. By looking at the sysadmin flag we can determine the role and raise a alarms. This can even be integrated with CI/CD systems as validation rule to block the deployments, if database updates are going via CI/CD pipeline.

---

SELECT loginname, sysadmin

FROM sys.syslogins

ORDER by sysadmin DESC

---

Happy reviewing.