logon triggers featuring EXECUTE AS is a good practice?

Question

logon triggers featuring EXECUTE AS is a good practice?

Lowell

SSC Guru

Points: 323518
More actions
January 3, 2011 at 2:25 pm

#229241

I've been fiddling with logon triggers again, and tripped over a few permissions issues where end users don't have rights to email, or write to an audit table in a certain database, etc.
As a result, I've gotten into using a syslogin I created specifically for that purpose...disabled the login, but am using it for EXECUTE AS functionality to overcome these types of issues;
Are you doing the same thing with your logon triggers? Am I using a hammer to swat a fly by using over-reaching permissions?
/****** Object: Login [superman] Script Date: 01/03/2011 16:17:48 ******/
CREATE LOGIN [superman] WITH PASSWORD=N'NotARealpassword', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=ON, CHECK_POLICY=ON
GO
EXEC sys.sp_addsrvrolemember @loginame = N'superman', @rolename = N'sysadmin'
GO
ALTER LOGIN [superman] DISABLE
GO
CREATE TRIGGER TR_Logon_Trigger
ON ALL SERVER FOR LOGON
AS
BEGIN
EXECUTE AS LOGIN='superman'
--do stuff in multiple databases that an end user has no rights to
END --Trigger
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!

Viewing 3 posts - 1 through 2 (of 2 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

I generally have login triggers use a sysadmin account. If you don't, and the trigger ends up blocking you from logging in, it's gets really messy really fast.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 2

Please read my little article called "Scope: The drastic caveat with Logon Triggers. " at http://www.sqlservercentral.com/articles/Administration/64974/

Main message: If you don't need to interrupt the connection at connect time, don't do it using a logon trigger !

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me