Sends a daily report with all user members of the sysadmin role or having ALTER ANY SERVER ROLE, CONTROL SERVER permissions

daily-check-high-privileges.sql

DECLARE @MailProfile sysname = N'';
DECLARE	@AgentOperator sysname = '';
DECLARE	@MailRecipients nvarchar(max) = (select email_address from msdb..sysoperators WHERE [name] = @AgentOperator);
DECLARE	@MailSubject nvarchar(max) = N'Daily users with high privileges on server ' + @@SERVERNAME;
DECLARE	@MailQueryTable nvarchar(max) = 
	N'<h1>Daily users with high privileges on server ' + @@SERVERNAME + N' report</h1>'
	+ N'<table border="1">'
	+ N'<tr><th>Instance Name</th><th>Login Name</th><th>Login Type</th><th>Creation Date</th><th>Permission Type</th></tr>'
	+ CAST((
			SELECT
				td = SQLInstanceName, '',
				td = LoginName, '',
				td = LoginType, '',
				td = CreateDate, '',
				td = Permission, ''
			FROM
			(
				SELECT	@@SERVERNAME AS SQLInstanceName, L.[name] AS LoginName, [type_desc] AS LoginType
					,	create_date AS CreateDate, P.[permission_name] AS Permission
				FROM	sys.server_permissions AS P
				JOIN	sys.server_principals AS L
				ON	P.grantee_principal_id = L.principal_id
				WHERE	permission_name IN ('ALTER ANY SERVER ROLE', 'CONTROL SERVER')
				AND	L.is_disabled = 0
				UNION ALL
				SELECT	@@SERVERNAME AS SQLInstanceName, L.[name] AS LoginName, [type_desc] AS LoginType
					,	create_date AS CreateDate, N'Sysadmin Role' AS Permission
				FROM	sys.server_principals AS L
				WHERE	IS_SRVROLEMEMBER('sysadmin', [name]) = 1
				AND	L.is_disabled = 0
			) AS T
			FOR XML PATH('tr'), TYPE
        ) AS nvarchar(max))
	+ N'</table>';

EXEC msdb.dbo.sp_send_dbmail
	@profile_name = @MailProfile
,	@recipients = @MailRecipients
,	@subject = @MailSubject
,	@body = @MailQueryTable
,	@body_format = 'HTML';