DDL Trigger Unable to access under security context

Question

DDL Trigger Unable to access under security context

Erin Ramsay

SSCrazy Eights

Points: 9728
More actions
July 17, 2014 at 1:19 pm

#288689

I have an interesting issue which may have a simple solution but I'm not seeing it.
There are mysterious forces at work in one my databases and I'm setting up a DML trigger to track any changes in the schema. Here are the components:
DatabaseA.dbo.DMLAuditEvent is the table to hold the information.
Sandbox contains the trigger.
"AuditUser" is a SQL Login mapped to both databases and member of db_owner on both.
USE sandbox
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [DDLAudit] ON DATABASE
WITH EXECUTE AS 'AuditUser'
FOR DDL_DATABASE_LEVEL_EVENTS AS
BEGIN
DECLARE @event XML
DECLARE @LoginTime DATETIME
DECLARE @LoginName VARCHAR(100)
DECLARE @DatabaseName VARCHAR(255)
DECLARE @ObjectName VARCHAR(255)
DECLARE @Command VARCHAR(max)
IF eventdata() IS NOT NULL
BEGIN
SET @Event = EVENTDATA()
SET @LoginTime = (SELECT CAST(replace(CAST(isnull(@event.value('(/EVENT_INSTANCE/PostTime)[1]', 'varchar(100)'), cast(getdate() AS VARCHAR)) AS VARCHAR(64)), 'T', ' ') AS DATETIME))
SET @LoginName = (SELECT CAST(isnull(@event.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(100)'), 'Unknown') AS VARCHAR(100)))
SET @DatabaseName = (SELECT isnull(CAST(@event.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(100)') AS VARCHAR(100)), 'Unknown'))
SET @ObjectName = (SELECT isnull(CAST(@event.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(100)') AS VARCHAR(100)), 'Unknown'))
SET @Command = (SELECT isnull(CAST(@event.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'varchar(max)') AS VARCHAR(max)), 'Unknown'))
INSERT INTO DatabaseA..DDLAuditEvents (
DDL_Event_Time
,DDL_Login_Name
,DDL_Database_Name
,DDL_Object_Name
,DDL_Command
)
VALUES (
@LoginTime
,@LoginName
,@DatabaseName
,@ObjectName
,@Command
)
END
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
So.. here is the issue. When the trigger is active and fires from Sandbox it fails to insert into DatabaseA..DDLAuditEvents with the following message:
Msg 916, Level 14, State 1, Procedure DDLAudit, Line 122
The server principal "AuditUser" is not able to access the database "dbadmin" under the current security context.
When DDLAuditEvents is created in Sandbox and the trigger is redirected it fires and operates just fine.
How is it that AuditUser is not able to access the other database when it's an owner of both?
Regards,
Erin

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

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

ShaunF SSChasing Mays Points: 612 More actions · Answer 1

Might be a silly question Erin but did you try logging on the SSMS using the Audit SQL login and see if you can access both the databases?

Erin Ramsay SSCrazy Eights Points: 9728 More actions · Answer 2

That was an excellent question, Shaun, as I hadn't thought to look at that but the answer is that AuditUser can access and perform dml statements against both databases.

Lowell SSC Guru Points: 323518 More actions · Answer 3

i'm thinking it might be the current database for the login might be affecting the rights?

if my current database context was master, for example, could that affect the permissions if it was a fully qualified update command??

EXECUTE AS LOGIN-'AuditUser';

select SUSER_NAME();

USE master;

ALTER TABLE SandBox.dbo.[TEST] ADD DDLTest2 varchar(30) ;

REVERT;

i think if you were to GRANT INSERT ON DatabaseA..DDLAuditEvents TO PUBLIC; would resolve the issue?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Erin Ramsay SSCrazy Eights Points: 9728 More actions · Answer 4

That's an interesting thought and I tried your code, which executed just fine, except of course if you execute it from another context other than master at which point the revert gives you an error because you've changed the context and can't revert. Also, running an insert from sandbox to dbadmin and vice versa works fine when executed via similar code to what you've provided. It's only when the fully qualified insert is run from inside the database DDL trigger that I get the error message. Granting Insert to Public didn't help. Wouldn't the security context of a database trigger be that database?