November 16, 2011 at 9:34 am
Hi,
I have created a DDL trigger to fire on the ALTER LOGIN statement. I then want to collect the event data and update a table called AlterLogin in a database called IT.
The problem I am facing is that potentially any user could issue the alter login statement, so I can't map them all to the IT databases to explicitly give them permissions to insert data into the table.
I have also tried getting the eventdata emailed to me instead of updating a table, but that also caused problems because not every user has permissions to execute sp_notify_operator
If someone could nudge me in the right direction, it would be most appreciated. Am I missing something obvious or am I tackling this the wrong way?
Thanks,
Bodsda
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create trigger [LOGIN_ALTER2]
on ALL SERVER
for ALTER_LOGIN
as
declare @data xml
declare @temp1 nvarchar(max)
declare @temp2 nvarchar(max)
declare @temp22 datetime
declare @temp3 int
declare @temp4 nvarchar(max)
declare @temp5 nvarchar(max)
declare @temp6 nvarchar(max)
declare @temp7 nvarchar(max)
declare @message nvarchar(max)
declare @temp8 nvarchar(max)
declare @temp9 nvarchar(max)
declare @temp10 nvarchar(max)
set @data = eventdata()
set @temp1 = @data.value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(max)')
set @temp22 = @data.value('(/EVENT_INSTANCE/PostTime)[1]','datetime')
set @temp3 = @data.value('(/EVENT_INSTANCE/SPID)[1]','int')
set @temp4 = @data.value('(/EVENT_INSTANCE/ServerName)[1]','nvarchar(max)')
set @temp5 = @data.value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(max)')
set @temp6 = @data.value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(max)')
set @temp7 = @data.value('(/EVENT_INSTANCE/ObjectType)[1]','nvarchar(max)')
set @temp8 = @data.value('(/EVENT_INSTANCE/DefaultLanguage)[1]','nvarchar(max)')
set @temp9 = @data.value('(/EVENT_INSTANCE/DefaultDatabase)[1]','nvarchar(max)')
set @temp10 = @data.value('(/EVENT_INSTANCE/LoginType)[1]','nvarchar(max)')
set @temp2 = cast(@temp22 as nvarchar(50))
insert into it.dbo.AlterLogin
values (@temp1, @temp2, @temp3, @temp4, @temp5, @temp6, @temp7, @temp8, @temp9, @temp10)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ENABLE TRIGGER [LOGIN_ALTER2] ON ALL SERVER
November 16, 2011 at 9:57 am
you can grant insert permissions to public for it.dbo.AlterLogin, i *think* that will resolve the audit issue;
i've done the same, but i've been sticking my audit table in master.
USE master;
CREATE TABLE [dbo].[DDLEventLog](
[EventDate] [datetime] NOT NULL,
[UserName] [sysname] NOT NULL,
[objectName] [sysname] NOT NULL,
[CommandText] [varchar](max) NOT NULL,
[EventType] [nvarchar](100) NULL
)
--everyone needs access to this table for audit purposes.
GRANT INSERT,UPDATE ON [dbo].[DDLEventLog] TO PUBLIC
Lowell
November 16, 2011 at 10:07 am
You could use Execute As, http://msdn.microsoft.com/en-us/library/ms188354.aspx or perhaps signing the trigger, http://www.sommarskog.se/grantperm.html#Certificates. I'm not positive that you can sign a DDL trigger.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 17, 2011 at 4:02 am
CREATE TRIGGER [DatabaseChangeTrigger] ON ALL SERVER
FOR DDL_EVENTS
AS
BEGIN
DECLARE @data XML;
DECLARE @schema SYSNAME;
DECLARE @object SYSNAME;
DECLARE @eventType SYSNAME;
DECLARE @user SYSNAME;
DECLARE @Mess NVARCHAR(MAX);
DECLARE @dbname SYSNAME;
SET @user = SUSER_NAME()
SET @data = EVENTDATA();
SET @eventType = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'SYSNAME');
SET @schema = @data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'SYSNAME');
SET @object = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME');
SET @dbname = @data.value('(/EVENT_INSTANCE/DatabaseName)[1]','SYSNAME');
EXECUTE AS LOGIN = 'mail'
INSERT Database.Schema.Table
(
[PostTime],
[DatabaseUser],
[Event],
[DatabaseName],
[Schema],
[Object],
[TSQL],
[XmlEvent]
)
VALUES
(
GETDATE(),
CONVERT(SYSNAME, @user),
@eventType,
@dbname,
CONVERT(SYSNAME, @schema),
CONVERT(SYSNAME, @object),
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(MAX)'),
);
SET @Mess =
'<FONT FACE="Arial"> User = ' + '<B>' + UPPER(CONVERT(SYSNAME, @user)) + '</B>' + ' has executed the following DDL operation'
+ '
'
+ 'DDL Operation = ' + '<B>' + @eventType + '</B>'
+ '
'
+ 'Object = ' + '<B>' + @dbname + '.' + CONVERT(SYSNAME, @schema) + '.' + CONVERT(SYSNAME, @object) + '</B></FONT>';
EXECUTE MSDB.DBO.sp_send_dbmail @profile_name = '.....', @recipients = '.....', @subject = 'Server level change detected ', @body = @Mess, @body_format = 'HTML'
REVERT
END
GO
ENABLE TRIGGER [DatabaseChangeTrigger] ON ALL SERVER
GO
Created the login "mail", granted impersonation rights on public to mail, mail is given database mail access in msdb.
Just need sign off from our op director to change the trigger so that it rolls back any changes unless you are part of the DBA team, which will annoy the developers.
October 30, 2017 at 3:53 am
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply