Sysadmin role change notification

Question

Post reply

Sysadmin role change notification

seanthomas2010

Old Hand

Points: 323
More actions
February 12, 2016 at 8:56 am

#304663

Hi
I need to capture when sysadmin roles change on any database on the server. also need to set up an email notification to let the dba team know.
to who would one set this up please
cheers
Sean

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Michael L John One Orange Chip Points: 27038 More actions · Answer 1

There are a couple things you can do.

You can run a query on a regular basis to test for new members. This may help get you started.

SELECT

sp1.[name] Login_Name,

sp2.[name] Server_Role

FROM sys.server_principals sp1

INNER JOIN sys.server_role_members srm ON sp1.principal_id = srm.member_principal_id

INNER JOIN sys.server_principals sp2 ON srm.role_principal_id = sp2.principal_id

WHERE sp2.[name] = 'sysadmin';

You would probably need to create a table to hold the original values and compare on a regular basis.

You can also create a trigger on sys.server_principals / sys.server_role_members to send an email if there has been an insert or an update.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Maddave SSCertifiable Points: 6828 More actions · Answer 2

Shameless plug:

If you have Redgate SQL Monitor, you could use my custom metric:

http://sqlmonitormetrics.red-gate.com/principals_with_sysadmin_login/

Even if you dont have sql monitor, I am sure you could adapt it to count the sysadmins and compare against a stored value.

As an aside, I pair this metric alert with a server trigger which sends an email if a login is added or updated. Thereby notifying me who added a login to the sysadmin role in the first place. I believe I got this code originally from someone on here, but cannot remember who, so cannot give kudos I'm afraid.

USE [master]

GO

CREATE TRIGGER [ddl_trig_LoginEvents]

ON ALL SERVER

FOR CREATE_LOGIN, ALTER_LOGIN, DROP_LOGIN

AS

-- Declare variables

DECLARE @mailSubject NVARCHAR(100);

DECLARE @mailBody NVARCHAR(MAX);

DECLARE @data XML;

DECLARE @eventType NVARCHAR(MAX);

DECLARE @newuser NVARCHAR(MAX);

DECLARE @creationuser NVARCHAR(MAX);

DECLARE @sql NVARCHAR(MAX);

-- Set Data

SET @data = EVENTDATA();

SET @newuser = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(max)');

SET @creationuser = @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(max)');

SET @eventType = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(max)');

SET @sql = @data.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')

---- Set the email data

SET @mailSubject = 'New SQL Login Event ' + @eventType + ' occurred on: ' + @@SERVERNAME;

SET @mailBody = 'A login event was detected on the SQL Server: ' + @@SERVERNAME + '

' +

'User name affected: ' + ISNULL(@newuser, 'Null User Name') + '

' +

'Event type : ' + ISNULL(@eventType, 'Null event') + '

' +

'Performed by user: ' + ISNULL(@creationuser, 'Null Created User') + '

' + +

'On date: ' + CONVERT(NVARCHAR, GETDATE(), 13) + '

' +

'SQL command executed (if any): ' + ISNULL(@sql, 'No SQL') + ' ' +

'Please verify why this login was edited on the server!' + '

' ;

--Send the mail

EXEC msdb.dbo.sp_send_dbmail

@recipients=N'<enterEmailAddress>',

@subject=@mailSubject,

@body = @mailBody,

@profile_name = '<enterMailProfile>',

@body_format = HTML;

GO

ENABLE TRIGGER [ddl_trig_LoginEvents] ON ALL SERVER

GO

seanthomas2010 Old Hand Points: 323 More actions · Answer 3

Thanks.

Was planning on having an archived table and an holding table for this. as this is going to be a a general report for the rest of the roles.

was looking for a way to set up an instant notification like a trigger. Thanks for this script

cheers

Eric M Russell SSC Guru Points: 125522 More actions · Answer 4

Another issue is when a domain group (ie: MYCORP\ProductionDBA), which is a member of SYSADMIN sqlserver group, has a new domain member added. That doesn't trigger any event or meta data change in SQL Server.

From within SQL Server, you can list members of a domain group like so:

exec xp_logininfo 'MYCORP\ProductionDBA','members';

Query accounts, domain groups, and members who have admin membership.

http://www.sqlservercentral.com/articles/Security/76919/

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho