We had a requirement on a server supporting a vendor application to monitor users who are given sysadmin rights. This was because vendor personnel would need these privileges every now and then – and it was our job to monitor when they are active and see that they are disabled on time. To achieve this objective I was asked to write a stored procedure based job that would get a list of people who were SA’s and send an email to the DBA team manager with the list. Below is the procedure I wrote to achieve this objective.
/*********************************************************************************************
Authored by: Malathi Mahadevan 2/27/2013
Tested on : SQL Server 2005 and 2008 only
Version 1.00
**********************************************************************************************/
CREATE PROCEDURE usp_getsanames
AS
DECLARE @tableHTML NVARCHAR(MAX)
Declare @T table(saname varchar(100),sarole varchar(500),saenabled smallint)
BEGIN
— SET NOCOUNT ON added to prevent extra result sets from
— interfering with SELECT statements.
SET NOCOUNT ON;
INSERT INTO @T(saname,sarole,saenabled)
— Insert statements for procedure here
SELECT mem.name,mem.type_desc,mem.is_disabled
FROM sys.server_role_members AS srm
INNER JOIN sys.server_principals AS rol ON rol.principal_id = srm.role_principal_id
INNER JOIN sys.server_principals AS mem ON mem.principal_id = srm.member_principal_id
WHERE rol.name = ‘sysadmin’
and mem.type_desc <> ‘SQL_LOGIN’
SET @tableHTML = N'<H1>SYSADMIN ROLE REPORT</H1>’ +
N'<table border=”1″>’ +
N'<tr><th>Name</th><th>Role</th><th>Status</th>’
+
CAST ((
SELECT td = CAST(([saname]) AS nvarchar(100)),”,
td = CAST((sarole) as nvarchar(15)),”,
td = CAST((CASE [saenabled]
WHEN 0 THEN ‘ENABLED’
WHEN 1 THEN ‘Disabled’
END) AS NVARCHAR(50))
FROM @T
–ORDER BY [name]
FOR XML PATH(‘tr’), TYPE) AS NVARCHAR(MAX) ) + N'</table>’
IF @@ROWCOUNT > 0
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@recipients =
‘dbamanager@mycompany.com’,
@subject =
‘SYSADMIN ROLE MEMBERS -VENDOR’, @importance = ‘High’, @body = @tableHTML, @body_format = ‘HTML’
end
ELSE
BEGIN
SELECT @TABLEHTML = N'<H1>SYSADMIN ROLE REPORT</H1>’ +
N'<table border=”1″>’ +
N'<tr><th>No records found</th>’
END
END
diligentdba 