Setting the trigger to EXECUTE WITH 'sa' appeared to do the trick. Not sure if this introduces any security concerns. I tried creating a separate login with permissions only to the Tools database and DDLEvent table but non sysadmin users got errors.
CREATE TRIGGER LogDDLEvent
ON ALL SERVER
WITH EXECUTE AS 'sa'
FOR DDL_EVENTS
AS
DECLARE @eventInfo XML
SET @eventInfo = EVENTDATA()
INSERT INTO Tools.audit.DDLEvent
VALUES
(
REPLACE(CONVERT(VARCHAR(128), @eventInfo.query('data(/EVENT_INSTANCE/PostTime)')),'T', ' ') -- EventTime
, CONVERT(VARCHAR(128), @eventInfo.query('data(/EVENT_INSTANCE/EventType)')) -- EventType
, CONVERT(VARCHAR(128), @eventInfo.query('data(/EVENT_INSTANCE/LoginName)')) -- LoginName
, CONVERT(VARCHAR(128), @eventInfo.query('data(/EVENT_INSTANCE/UserName)')) -- UserName
, CAST(HOST_NAME() AS VARCHAR(128)) -- MachineName
, (SELECT CAST(client_net_address AS VARCHAR(128))
FROM sys.dm_exec_connections
WHERE Session_id = CONVERT(INT, @eventInfo.value('data(/EVENT_INSTANCE/SPID)[1]', 'int'))) -- IPAddress
, CONVERT(VARCHAR(128), @eventInfo.query('data(/EVENT_INSTANCE/DatabaseName)')) -- DatabaseName
, CONVERT(VARCHAR(128), @eventInfo.query('data(/EVENT_INSTANCE/SchemaName)')) -- SchemaName
, CONVERT(VARCHAR(128), @eventInfo.query('data(/EVENT_INSTANCE/ObjectName)')) -- ObjectName
, CONVERT(VARCHAR(128), @eventInfo.query('data(/EVENT_INSTANCE/ObjectType)')) -- ObjectType
, CONVERT(VARCHAR(MAX), @eventInfo.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)')) -- DDLCommand
, @eventInfo -- DDLEventXML
)
Try
CREATE TRIGGER tg_ai_tablea
AFTER INSERT ON tablea
FOR EACH ROW
INSERT INTO tableb (userid, value)
VALUES (NEW.userid, CASE NEW.type WHEN 'admin' THEN 1 WHEN 'user' THEN 2 END);
Here is SQLFiddle demo
You can also consider instead of storing value
just evaluating it on the fly
SELECT b.userid,
CASE a.type WHEN 'admin' THEN 1 WHEN 'user' THEN 2 END value
-- , other columns from tableb
FROM tableb b JOIN tablea a
ON b.userid = a.user.id;
Best Answer
Your best option here is to create a Stored procedure to handle everything.
On user login, the stored procedure is called with the username and password(Encrypted) for example.
This stored procedure would do the following
This would be the best way to handle the process. I would then wrap the above in a transaction, to ensure that the audit record is always created for any login. If the transaction fails, the user will have to login again.
Here is a very basic example for the sake of keeping it simple. It can definitely be improved on for your specific use case, but this is broken down for easy reading and logic, especially around the password validation as encryption should be used but is not for this example :)
The login check and LastLogin Update could probably be combined. You could also use a try catch instead of a transaction. In this case I believe the difference in negligible. Also, as mentioned, you would want to have your passwords encrypted. What i liked to do, was encrypt the password in the client, and only compare encrypted strings. That means you never need to decrypt the password. Its always encrypted and therefore i believe is more secure.