SQL Server 2012 Concurrent Sessions

I need help in implementing the following:

Implement logon triggers to restrict users from logging on multiple times.
LVL 1
jasonhdzAsked:
Who is Participating?
 
Anthony PerkinsConnect With a Mentor Commented:
See if this works for you (but be careful and don't lock yourself out):
USE master;
GO
CREATE TRIGGER connection_limit_trigger ON ALL SERVER
    FOR LOGON
AS
    BEGIN
        IF (SELECT  COUNT(*)
            FROM    sys.dm_exec_sessions
            WHERE   is_user_process = 1
                    AND original_login_name = ORIGINAL_LOGIN()
           ) > 3
            ROLLBACK;
    END;

Open in new window

0
 
Anthony PerkinsCommented:
You do realize that there is a perfectly good example in SQL Server BOL for CREATE TRIGGER (Transact-SQL), right?  Look for Example F
0
 
jasonhdzAuthor Commented:
No i did not, that is why i asked.  But thanks!  I will check it out!
0
 
jasonhdzAuthor Commented:
USE master;
GO
CREATE TRIGGER connection_limit_trigger
ON ALL SERVER WITH EXECUTE AS "JAY-PC\testdba1"
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()= "JAY-PC\testdba1" AND
    (SELECT COUNT(*) FROM sys.dm_exec_sessions
            WHERE is_user_process = 1 AND
                original_login_name = "JAY-PC\testdba1") > 3
    ROLLBACK;
END;
0
 
jasonhdzAuthor Commented:
from the scrip above, i have the following issues:

1.  It looks like the If is looking for a specific user, i need to implement this for every single user login in the db server.
2.  Only Windows Login is implemented.  It does not appear to recognize the Windows User Identificator
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.