October 18, 2011 at 6:56 am
I've been testing a logon trigger that will not allow connection if program name is MS office. However, I need to allow a couple logins ('sa' and 'Finance') access to sql from MS office. The trigger below works, but only for all users. Any suggestions? Thanks
CREATE TRIGGER application_limit_trigger
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
IF EXISTS (SELECT *
FROM sys.dm_exec_sessions
WHERE
program_name IN (N'2007 Microsoft Office System', N'Microsoft Office 2010')
and LOGIN_NAME NOT IN ('sa', 'finance'))
ROLLBACK;
END;
October 18, 2011 at 7:26 am
Doesn't the login takes place BEFORE any session is created, and thus before any rows could exist in sys.dm_exec_sessions?
I think you need something testing ORIGINAL_LOGIN() and
APP_NAME() is what you want instead:
as always, when testing a logon trigger, keep the original window you use to create/enable the trigger open so you can disable or drop the trigger....don't lock yourself out!
CREATE TRIGGER application_limit_trigger
ON ALL SERVER --no need to execute as 'sa' to get access to DMVs
FOR LOGON
AS
BEGIN
IF HOST_NAME() IN (N'2007 Microsoft Office System', N'Microsoft Office 2010')
AND ORIGINAL_LOGIN() NOT IN ('sa', 'finance')
BEGIN
RAISERROR('Office connections are not permitted with this logon.', 16, 1)
ROLLBACK;
ENDEND;
Lowell
October 18, 2011 at 9:13 am
Lowell (10/18/2011)
Doesn't the login takes place BEFORE any session is created, and thus before any rows could exist in sys.dm_exec_sessions?I think you need something testing ORIGINAL_LOGIN() and
APP_NAME() is what you want instead:
You are correct! "ORIGINAL_LOGIN" is exactly what I needed.
While reading through the forum, I came across this warning (below) from Gila Monster and was hoping you might be able explain a bit. Is this where the RAISERROR statement comes into play during a sev. 16 error? Could the SA account be locked? I want to be prepared for this before I put the trigger into prod. Suppose I could emulate a sev. 16 in test env.
Thanks!
"Be very, very careful when writing login triggers. If, for any reason, the trigger fires an error of Sev 16 or higher (object does not exist, database not found, permission denied) the trigger fails and rolls back the login.
I've had a couple panicked, late-night phone calls because of these."
October 18, 2011 at 9:22 am
an example might be a logon trigger that is supposed to write to an audit table...suppose it said INSERT INTO MyAuditTable
SELECT ....
what's the database context? depending on the user, it might try to use master.dbo.MyAuditTable, or the users default database like PRODUCTION.dbo.MyAuditTable, where it does not exist.
What if the table is in the AUDIT database, and it's writing to AUDIT.dbo.MyAuditTable, but the end user has no access to that datbase?
what if the end user does not have permissions to that table, even if it is the right db context?
all of the above are errors that would get raise with level 16 or above.
so for that example, you probably need to grant INSERT permissions to public on the table, and fix the INSERT to use a full three part name.
the caveat she's refering to is to make sure the work you are doing will not fail for the "little people" who don't run around with sysadmin privilesdges like yourself, where security and other assumptions might be taken for granted.
Lowell
October 18, 2011 at 9:31 am
the caveat she's refering to is to make sure the work you are doing will not fail for the "little people" who don't run around with sysadmin privilesdges like yourself, where security and other assumptions might be taken for granted.
Got it.....she was referring to late night phone calls that stemmed from end users, rather than dba's. I was also testing with a test/end user login for this reason.
Thank you for the clarification.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply