April 3, 2014 at 9:50 am
I have created a DDL Trigger in order to track who creates/modifies/deletes SP and Tables
CREATE TRIGGER DDLTrigger_OBJECT
--ALTER TRIGGER DDLTrigger_OBJECT
ON DATABASE
WITH ENCRYPTION
FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE, CREATE_TABLE, ALTER_TABLE, DROP_TABLE
but anyone who tries to create/delete an objects receives an error:
Msg 297, Level 16, State 1, Procedure DDLTrigger_OBJECT, Line 14
The user does not have permission to perform this action.
I have tried to grant execute but cannot as
GO
GRANT EXECUTE ON [DDLTrigger_OBJECT] TO
GO
Cannot find the object 'DDLTrigger_OBJECT', because it does not exist or you do not have permission.
I'd like to allow the creation/deletion to happen, but it should notifiy me by email.
Who do i allow anyone to execute the DDLTrigger?
Thank you
April 3, 2014 at 10:14 am
the issue is not the trigger, but the tables/objects the trigger is manipulating.
for example, if your trigger is inserting into master.dbo.MyAuditTable, you probably want to GRANT INSERT ON master.dbo.MyAuditTable TO PUBLIC;
that way anyone who creates an object will let the audit enter the required data.
if you can post your entire trigger, we can show you exactly what the end user wouldn't have permissions to.
Lowell
April 3, 2014 at 11:52 am
CREATE TRIGGER DDLTrigger_OBJECT
--ALTER TRIGGER DDLTrigger_OBJECT
ON DATABASE
WITH ENCRYPTION
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_Admin.dbo.DDLEvents
(
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();
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DBA',
@recipients = 'name',
@body = 'New Object created/modified/dropped in DB - dbname',
@subject = 'Object Alert' ;
i then used:
GRANT INSERT ON DBA_Admin.dbo.DDLEvents TO [Developers]
GO
GRANT EXECUTE ON msdb.dbo.sp_send_dbmail TO [Developers]
GO
April 3, 2014 at 3:36 pm
most users cannot run this unless you grant it explicitly either: (GRANT VIEW SERVER STATE TO [myDomain\developers]
)
SELECT client_net_address
FROM sys.dm_exec_connections
WHERE session_id = @@SPID
is [Developers] a role, or a placehoder for your AD Group?
your developers need to be users in the msdb database to use sp_send_dbmail.
my developers are in a domain group, so i tend to simply amd them as users to the msdb database, and then also the DatabaseMailUserRole
USE [msdb]
GO
CREATE USER [myDomain\developers] FOR LOGIN [myDomain\developers]
GO
USE [msdb]
GO
EXEC sp_addrolemember N'DatabaseMailUserRole', N'myDomain\developers'
GO
ADD
Lowell
April 4, 2014 at 9:46 am
Thank you for your assistance! much appreciated!
July 28, 2014 at 6:56 am
Hi,
I am now experiencing an issue where 'SQL' users are not allowed to insert into the DDLEVENTS table?
Does granting 'public' only work with windows AD users and group?
The server principal "TAP" is not able to access the database "DBA_Admin" under the current security context.
Appreciate your feedback..
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply