March 22, 2017 at 8:44 am
Hi Experts,
Checking if there is any script to send out an email whenever any permission is GRANTED OR REVOKED to a login or database user.
Basically whenever certain 'x' has granted some 'y' permission for a login / database user, that TSQL along with 'x' user info should be sent as an email to the dbagroup via database mail
Any such script available? Is this achievable?
Thank you.
Sam
March 22, 2017 at 1:22 pm
I don't know if you can get an e-mail at the time when a permission change happens, I've created reports before using the default trace like the following code:
DECLARE @path NVARCHAR(260), @StartDate datetime, @EndDate datetime;
SELECT @path = REVERSE(SUBSTRING(REVERSE([path]), CHARINDEX('\', REVERSE([path])), 260)) + N'log.trc'
FROM sys.traces
WHERE is_default = 1;
SET @StartDate = DateAdd(hour, DateDiff(hour, 1, GetDate()), 0);
SET @EndDate = GetDate();
SELECT LEFT(td.DatabaseName,16) AS db_name, CAST(td.TextData AS VARCHAR(60)) AS Command, CONVERT(varchar(20), td.StartTime, 120) AS StartTime,
LEFT(td.LoginName,25) AS LoginName, LEFT(td.HostName,10) AS HostName, LEFT(td.ApplicationName,40) AS ApplicationName
FROM sys.fn_trace_gettable(@path, DEFAULT) td
INNER JOIN sys.trace_events te ON td.EventClass = te.trace_event_id
WHERE td.EventClass IN (102,103,104,105,106,108,109,110,111, 152, 153)
AND td.DatabaseName <> 'tempdb'
AND StartTime BETWEEN @StartDate AND @EndDate
ORDER BY StartTime;
March 22, 2017 at 2:30 pm
Chris Harshman - Wednesday, March 22, 2017 1:22 PMI don't know if you can get an e-mail at the time when a permission change happens, I've created reports before using the default trace like the following code:
DECLARE @path NVARCHAR(260), @StartDate datetime, @EndDate datetime;
SELECT @path = REVERSE(SUBSTRING(REVERSE([path]), CHARINDEX('\', REVERSE([path])), 260)) + N'log.trc'
FROM sys.traces
WHERE is_default = 1;SET @StartDate = DateAdd(hour, DateDiff(hour, 1, GetDate()), 0);
SET @EndDate = GetDate();SELECT LEFT(td.DatabaseName,16) AS db_name, CAST(td.TextData AS VARCHAR(60)) AS Command, CONVERT(varchar(20), td.StartTime, 120) AS StartTime,
LEFT(td.LoginName,25) AS LoginName, LEFT(td.HostName,10) AS HostName, LEFT(td.ApplicationName,40) AS ApplicationName
FROM sys.fn_trace_gettable(@path, DEFAULT) td
INNER JOIN sys.trace_events te ON td.EventClass = te.trace_event_id
WHERE td.EventClass IN (102,103,104,105,106,108,109,110,111, 152, 153)
AND td.DatabaseName <> 'tempdb'
AND StartTime BETWEEN @StartDate AND @EndDate
ORDER BY StartTime;
Thanks Chris. Very nice one. However, I want to something an automated email gets fired as and one someone changes any server role, server permission , database role or database permission.
In our environment team everyone has full access. We cant do much about it. But still want to see who is giving what permissions to whom??
March 22, 2017 at 3:10 pm
Haven't tried it but it seems like it would be doable with event notifications. There is an example in one of the articles up here, (probably others as well):
Getting Started with SQL Server Event Notifications
Sue
March 22, 2017 at 3:32 pm
maybe a database level DDL trigger like this?
CREATE TRIGGER audit_Security_events ON DATABASE --ALL SERVER --DATABASE
FOR GRANT_DATABASE, DENY_DATABASE, REVOKE_DATABASE
AS
DECLARE @event xml, @subject NVARCHAR(80), @body NVARCHAR(4000);
BEGIN
SET @event = EVENTDATA();
SET @body = N'Event Type: ' + CAST(@event.query('data(/EVENT_INSTANCE/EventType)') AS NVARCHAR(80)) + NCHAR(13) + NCHAR(10) +
N'Exec Login: ' + CAST(@event.query('data(/EVENT_INSTANCE/LoginName)') AS NVARCHAR(80)) + NCHAR(13) + NCHAR(10) +
N'Type: ' + CAST(@event.query('data(/EVENT_INSTANCE/ObjectType)') AS NVARCHAR(10)) + NCHAR(13) + NCHAR(10) +
N'Database: ' + CAST(@event.query('data(/EVENT_INSTANCE/DatabaseName)') AS NVARCHAR(80)) + NCHAR(13) + NCHAR(10) +
N'Object: ' + CAST(@event.query('data(/EVENT_INSTANCE/SchemaName)') AS NVARCHAR(80)) + N'.' + CAST(@event.query('data(/EVENT_INSTANCE/ObjectName)') AS NVARCHAR(10)) + NCHAR(13) + NCHAR(10) +
N'Grantor: ' + CAST(@event.query('data(/EVENT_INSTANCE/Grantor)') AS NVARCHAR(80)) + NCHAR(13) + NCHAR(10) +
N'Command: ' + CAST(@event.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)') AS NVARCHAR(1000));
--send e-mail
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'DBMaintenance',
@recipients = N'youremail@yourcompany.com',
@subject = N'Security Event',
@body = @body;
END
GO
March 22, 2017 at 8:56 pm
Thanks a lot for the help Chris.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply