Solved

SQL Server 2012 Concurrent Sessions

Posted on 2014-03-27
5
369 Views
Last Modified: 2014-04-08
I need help in implementing the following:

Implement logon triggers to restrict users from logging on multiple times.
0
Comment
Question by:jasonhdz
  • 3
  • 2
5 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39960541
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
 
LVL 1

Author Comment

by:jasonhdz
ID: 39960545
No i did not, that is why i asked.  But thanks!  I will check it out!
0
 
LVL 1

Author Comment

by:jasonhdz
ID: 39960554
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
 
LVL 1

Author Comment

by:jasonhdz
ID: 39960555
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
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 39960705
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

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

In this article, we will see the basic design consideration while designing a Multi-tenant web application in a simple manner. Though, many frameworks are available in the market to develop a multi - tenant application, but do they provide data, cod…
Synchronize a new Active Directory domain with an existing Office 365 tenant
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now