Security Prevent from SQL login user to close/kill other connections. RRS feed

  • Question

  • Hello,

    I'm looking for security option in SQL to prevent from SQL login user to close/kill other users connections/sessions on our application. 

    i Marked "ALTER any connection" option for specific user on the Server level - Securables -DENY checkbox.

    but still i'm able to close other user's connections with this specific user.

    Additional info:

    this user has server role: Public.

    and database role membership: db_datareader and db_datawriter.

    any suggestions?


    Monday, August 12, 2019 7:13 AM

All replies

  • As a default only sysadmin and processadmin users have the rights to kill processes they do not own.  Sysadmin users cannot be restricted in any way.   

    Monday, August 12, 2019 11:25 AM
  • Sounds like this user is a memeber of sysadmin.

    Run this:

    EXECUTE AS LOGIN = 'thislogin'
    SELECT * FROM sys.login_token

    Here you which token this login has. Most likely, you will find sysadmin. But you may also find for instance AD groups from which this user gets membership in sysadmin.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Monday, August 12, 2019 9:08 PM
  • Dear friend,

    With the permission that is passing, it should not be possible to kill the sessions.
    Validate if you are not using any users with dbo_owner permission or a sysadmin login.

    Jefferson Clyton Pereira da Silva - [MCSA | MCP | MCTS | MTA | Analista de Banco de Dados - Sql Server e Oracle ]

    Tuesday, August 13, 2019 12:40 AM