March 11, 2021 at 2:40 am
I recently had to create a logon trigger to track what appeared to be unused accounts, putting the logon, IP and time into a table in my Management database. The aim being to see if the accounts are still being used, when and from where. The code was pretty simple:
create table DBAManager.dbm.tbl_LogonTrack (
LogonTrackID int identity (1,1),
OriginalLogin nvarchar(256),
LogonIP nvarchar(50),
LogonDateTime datetime)
CREATE OR ALTER TRIGGER DBM_Track_Logon_Events
ON ALL SERVER
FOR LOGON
AS
BEGIN
declare @OriginalLogin nvarchar(256)
set @OriginalLogin = ORIGINAL_LOGIN()
if @OriginalLogin in ('user1', 'OldUser', 'temporaryuser')
begin
insert into DBAManager.dbm.tbl_LogonTrack
select @OriginalLogin, EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]','NVARCHAR(50)'), getdate()
end
END
The problem came when the accounts started trying to logon and failed with a trigger execution exception and security context problem. The root cause being that the management database was in Restricted_User mode, and the trigger runs in the Security Context of the account that caused it to fire. The simple solution was to set the DB to Multi_User mode and give each account db_datawriter rights on my management DB, which is not really ideal. Partly because I don't want users to write to my database, but also while this time it was only 7 users, I've worked on a client's server with over 700 SQL Logons, most of which would have been unused.
The question then is, is there some way to change the security context of the trigger, so it runs under an account with the right rights? I was thinking "EXECUTE AS" or setting up a proxy credential.
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
March 11, 2021 at 3:27 am
You can add an EXEC AS. Create a login that has only the specific authority it needs in the mgmt db to INSERT to the table; i.e., it doesn't need full db_datawriter, it just needs INSERT to that specific table.
...
ON ALL SERVER
WITH EXECUTE AS '<login_name>'
...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply