February 17, 2016 at 9:53 am
I have tools database. I created a table in tools database
CREATE TABLE [dbo].[DDLAudit](
[EventDate] [datetime] NOT NULL,
[EventType] [nvarchar](64) NULL,
[EventDDL] [nvarchar](max) NULL,
[EventXML] [xml] NULL,
[DatabaseName] [nvarchar](255) NULL,
[SchemaName] [nvarchar](255) NULL,
[ObjectName] [nvarchar](255) NULL,
[HostName] [varchar](64) NULL,
[IPAddress] [varchar](32) NULL,
[ProgramName] [nvarchar](255) NULL,
[LoginName] [nvarchar](255) NULL
) ON [PRIMARY]
I created DDL trigger in one of the user database and inserting the data into the table in the tools database. If the users doesn't have access to the tools database still it works?
Use UserDB
CREATE TRIGGER [Auditchanges]
ON DATABASE
FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE, CREATE_TABLE, ALTER_TABLE,DROP_TABLE
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@EventData XML = EVENTDATA();
DECLARE
@ip VARCHAR(32) =
(
SELECT client_net_address
FROM sys.dm_exec_connections
WHERE session_id = @@SPID
);
INSERT DBA_ToolsDB.dbo.DDLAudit
(
EventType,
EventDDL,
EventXML,
DatabaseName,
SchemaName,
ObjectName,
HostName,
IPAddress,
ProgramName,
LoginName
)
SELECT
@EventData.value('(/EVENT_INSTANCE/EventType)[1]', 'NVARCHAR(100)'),
@EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(MAX)'),
@EventData,
DB_NAME(),
@EventData.value('(/EVENT_INSTANCE/SchemaName)[1]', 'NVARCHAR(255)'),
@EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(255)'),
HOST_NAME(),
@ip,
PROGRAM_NAME(),
SUSER_SNAME();
END
Question:
Does it requires that the AuditTables should be in the same database where the trigger created?
February 17, 2016 at 10:11 am
I am able to test in lower environment.
Looks even though if you have dbowner permissions in the user database to create and drop the tables if you don't have permissions on the audit tables user can't able to perform the drop or create table.
When I tested I received the error
The user does not have permission to perform this action.
I just want to track the changes what they are doing but not to stop. How can we achieve this?
I can't use profiler not very familiar with extended events. This is Standard edition so is there any other option available?
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply