limit all users to 1 session

Question

I have been required to configure a SQL Server to only allow one session per login. I have found a few references on creating login triggers in order to prevent a login from establishing more than 1 session, but I am wondering if there is some way to define this at a lower level, so that this session limit is the default, rather than having to define this in another login for each user?

I am seeing a lot of references to this topic in both "questions that may already have your answer" and "similar questions" here on stackoverflow, but so far have either not found or not understood a post that describes what I am trying to do. I have also seen a reference about Declarative Management Framework that allows you to configure SQL Server by policy I think.

I am going to keep on looking through articles here to try to learn this, but in the meantime... advice very much appreciated!

Answer 1

The example for a logon trigger in Books Online is pretty close to what I think you want, I've made a few changes to make it work for all logins.

-- Trigger must be created by a user with 'view server state' permission in order the trigger to have unrestricted access to sys.dm_exec_sessions.
create trigger connection_limit_trigger on all server with execute as self for logon
as
begin
    -- Check whether the caller is a SysAdmin.
    -- Note: The trigger is executing under an elevated security context so we must switch to the caller's context to test their SysAdmin status.
    declare @IsSysAdmin int
    execute as caller
    set @IsSysAdmin = isnull(is_srvrolemember ('sysadmin'), 0)
    revert

    -- If the user is not a SysAdmin and there are already too many non-dormant sessions for this login then 'rollback' the logon.
    -- Note: sys.dm_exec_sessions does not include an entry for this logon attempt.
    if ((@IsSysAdmin = 0) and ((select count(1) from sys.dm_exec_sessions where is_user_process = 1 and status <> 'Dormant' and original_login_name = original_login()) > 1))
    begin
        raiserror('This login has exceeded the maximum of 1 connections to this SQL Server.', 16, 1)
        rollback
    end
end

I've added in a check so the limit doesn't apply to SysAdmin logins and doesn't count dormant connection pool connections. A couple of things to note;

• if a connection is not properly closed it can hang around in sys.dm_exec_sessions for a while, in this case the user won't be able to re-connect until the dead connection clears itself.
• if you mess up a logon trigger you can lock yourself (and everyone else!) out of the SQL Server. See the logon trigger page for details of how to get back in :)