locked
How to restrict Number of user in Sql Server 2008 R2 RRS feed

  • Question

  • Hello

    I want to achieve functionality like I want to restrict Number of users from being connected to my database in sql server.

    For example Suppose I have set limit for 5 users. Then if there are 5 users already connected to database & If 6th user try to connect to database then sql should throw some exception . Or sql should not allow that user to be connect to Database.

    So how to achieve this?

    Can any body please guide n Help me in detail please..

    Thanking you in advance..

    Regards

    Vipul Langalia

    Wednesday, November 27, 2013 10:30 AM

Answers

  • Hello Vipul,

    There is option in sp_configure to set value for MAX CONCURRENT USERS.Please read documentation before that

    http://technet.microsoft.com/en-us/library/ms187030.aspx

    Below query can be used to set limit

    sp_configure 'show advanced options',1
    go 
    reconfigure
    go
    sp_configure 'user connections',XX--value you want to set
    go
    reconfigure with override
    go

    Restart is required .Its a server configuration not database

    HTH


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers



    • Edited by Shanky_621MVP Wednesday, November 27, 2013 11:33 AM
    • Proposed as answer by Ramesh Babu Vavilla Wednesday, November 27, 2013 12:52 PM
    • Marked as answer by Fanny Liu Monday, December 9, 2013 1:11 AM
    Wednesday, November 27, 2013 10:51 AM

All replies

  • You need to use LOGON trigger.

    Eg:

    CREATE TRIGGER limit_Login_trigger
    ON ALL SERVER WITH EXECUTE AS 'loginname' FOR LOGON
    AS
    BEGIN
    IF ORIGINAL_LOGIN()= 'loginname' AND -- Remove the same if you do not want the login check
        (SELECT COUNT(*) FROM sys.dm_exec_sessions
                WHERE is_user_process = 1 AND
                    original_login_name = 'loginname') > 5 --number you wish to restrict as maximum
        ROLLBACK;
    END;

    Ref: http://sqlserverscribbles.com/2012/12/19/limiting-the-number-connections-to-sql-server-database/
    • Edited by SQLZealots Wednesday, November 27, 2013 11:02 AM
    Wednesday, November 27, 2013 10:51 AM
  • Hello Vipul,

    There is option in sp_configure to set value for MAX CONCURRENT USERS.Please read documentation before that

    http://technet.microsoft.com/en-us/library/ms187030.aspx

    Below query can be used to set limit

    sp_configure 'show advanced options',1
    go 
    reconfigure
    go
    sp_configure 'user connections',XX--value you want to set
    go
    reconfigure with override
    go

    Restart is required .Its a server configuration not database

    HTH


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers



    • Edited by Shanky_621MVP Wednesday, November 27, 2013 11:33 AM
    • Proposed as answer by Ramesh Babu Vavilla Wednesday, November 27, 2013 12:52 PM
    • Marked as answer by Fanny Liu Monday, December 9, 2013 1:11 AM
    Wednesday, November 27, 2013 10:51 AM
  • You need to use LOGON trigger.

    Eg:

    CREATE TRIGGER limit_Login_trigger
    ON ALL SERVER WITH EXECUTE AS 'loginname' FOR LOGON
    AS
    BEGIN
    IF ORIGINAL_LOGIN()= 'loginname' AND -- Remove the same if you do not want the login check
        (SELECT COUNT(*) FROM sys.dm_exec_sessions
                WHERE is_user_process = 1 AND
                    original_login_name = 'loginname') > 5 --number you wish to restrict as maximum
        ROLLBACK;
    END;

    Ref: http://sqlserverscribbles.com/2012/12/19/limiting-the-number-connections-to-sql-server-database/

    be aware that this only works, if the connection attempt goes directly to the database in question.

    If the user connects to master or some other database first, and later changes to the database in question, there will not be any Logon-Event which you can detect and abort via a Logon-Trigger.


    Andreas Wolter | Microsoft Certified Master SQL Server

    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com | www.SarpedonQualityLab.com

    Wednesday, November 27, 2013 11:32 AM