locked
controlling user access in sql server RRS feed

  • Question

  • Hello Guys.

     

    Can I restrict the number of times a user can access the sql server?

    For example, userA is using the database, if another person tries to access the database using the userA account while userA is still logged in.. SQL server denies that access or disconnects the former user.

    Can I implement that when I use it as a connection string to my windows application in c#.

     

    Any resource that I can dig in? ;)

     

    thanks a lot.

    Friday, November 19, 2010 6:57 AM

Answers

  • reate login AuditLogin with password = ‘AuditLoginPswd’
    go
    /*Create a very simple login trigger */
    create trigger AuditLogin_Demo
    /* server means instance level*/
    on all server
    with execute as self
    /* We specify the logon event at this stage
    – If there are more than one connections,
    – Issue a rollback*/
    for logon
    as begin
    IF ORIGINAL_LOGIN()= ‘AuditLogin’ AND
    (SELECT COUNT(*) FROM sys.dm_exec_sessions
    WHERE is_user_process = 1 AND
    original_login_name = ‘AuditLogin’) > 1
    ROLLBACK;

    end
    go


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Proposed as answer by PrinceLucifer Wednesday, November 24, 2010 6:57 AM
    • Marked as answer by Alex Feng (SQL) Friday, November 26, 2010 12:39 PM
    Friday, November 19, 2010 8:22 AM

All replies

  • Hi!

    I don't think there is a way to directly do that, but you could use a login trigger to accomplish that...

    Lucifer

    Friday, November 19, 2010 8:13 AM
  • reate login AuditLogin with password = ‘AuditLoginPswd’
    go
    /*Create a very simple login trigger */
    create trigger AuditLogin_Demo
    /* server means instance level*/
    on all server
    with execute as self
    /* We specify the logon event at this stage
    – If there are more than one connections,
    – Issue a rollback*/
    for logon
    as begin
    IF ORIGINAL_LOGIN()= ‘AuditLogin’ AND
    (SELECT COUNT(*) FROM sys.dm_exec_sessions
    WHERE is_user_process = 1 AND
    original_login_name = ‘AuditLogin’) > 1
    ROLLBACK;

    end
    go


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Proposed as answer by PrinceLucifer Wednesday, November 24, 2010 6:57 AM
    • Marked as answer by Alex Feng (SQL) Friday, November 26, 2010 12:39 PM
    Friday, November 19, 2010 8:22 AM
  • Thanks for the help!

    do I have to call this? or will this trigger be called automatically during windows startup?

    Wednesday, November 24, 2010 6:17 AM
  • The trigger will automatically called every time a user logs on
    Wednesday, November 24, 2010 6:57 AM
  • Just one note, IIRW this feature were introduced in SP2 of SQL Server 2005. so please make sure  you have lastest service pack
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, November 24, 2010 8:56 AM