Finding users who are Sysadmins

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: