Thanks a lot Greg. This script is really helpful.
Correct me If I am wrong.
My situation is I want to restrict group of accounts which can login from some applications but not from SSMS / Excel / DBArtisan / .....
As suggested by you the sql service account can be added to the [BlackList] with [RestrictionEnabled] to zero to avoid rollback right ?
Thanks & Regards,
Section #6 of the Trigger covers this scenario:
--If a particular application connects to SQL Server, with a given UserName (i.e. service account cannot connect with SSMS)
If(Exists(Select * from SQL_Audit.dbo.BlackList where AppName = @AppName and LoginName = @User and RestrictionEnabled = 1))
To set it up, insert to the blacklist table
User and application
Each user and application requires a seperate entry, i.e.
So update the table with each developer login and the application that cannot be used.
Alternatively, you could add a condition at the begining of the trigger:
If ((@User in ('User1', User2', 'User3')) and (@AppName in ('Excel', Access', 'TOAD')))
insert into SQL_Audit..Violations
(PostDate, LoginName, IPAddress, HostName, ServerName, AppName, ViolationType)
values (@PostTime, @User, @IPAddress, @HostName, @SrvName, @AppName, 'ApplicationName')
The advantage of the second method is that the hardcoded values require less disk IO to process; however, the disadvantage is that the SP would have to be recreated to every change in Users and apps.
/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!