Question: As part of a regular SQL Server Violations reporting and checking any vulnerabilities on SQL Servers, I run regular scans and report on elevated server roles. What is an efficient method to identify if a Login is using the sysadmin role?
Answer: I use the IS_SRVROLEMEMBER function , which allows you to check on various server roles. As well as sysadmin , it is possible to also check on serveradmin,dbcreator,setupadmin,bulkadmin,securityadmin,diskadmin and processadmin.
The IS_SRVROLEMEMBER returns either a value of 1 or 0. 1 = the login is a member of the role 0= the login is not a member of the role.
This an example of how to check if the 'BUILTIN\Administrators' login is a member of the sysadmin role, this is by using the login option
SELECT IS_SRVROLEMEMBER('sysadmin', 'BUILTIN\Administrators');
This is an example if the CURRENT login is a member of the sysadmin group
SELECT IS_SRVROLEMEMBER ('sysadmin');
Read more on managing the sysadmin role
Drop sysadmin with ALTER SERVER ROLE and DROP MEMBER ...
Powershell sql server security audit
select current_user shows dbo (SQL Server DBA)