SQL Server Performance

How Block specific User to access SQL Server

Discussion in 'General DBA Questions' started by sonnysingh, Apr 15, 2008.

  1. sonnysingh Member

    Hi All
    I want to restrict specific users from access SQL Server either block IP address OR disconnect users if using a specific IP address.....
    Thanks in Advance
    sonny

    sonnysingh, Apr 15, 2008
    #2

  2. jagblue New Member

    Hi
    Use sp_droplogin will drop current login to use sqlserver
    you can find more help on BOL
    Thanks
    jagblue, Apr 15, 2008
    #3

  3. sonnysingh Member

    Thanks .. but I do not want to drop the login as other user using this logins....
    sonnysingh, Apr 17, 2008
    #4

  4. ghemant Moderator

    Hi,
    You can have a list of IP address stored in some table and while users are connecting via application you can check the IP address against value stored in the IP Address table along with userid and password to restrict it or you can setup a firewall rule to restrict particular IP address.
    hth
    ghemant, Apr 18, 2008
    #5

  5. Adriaan New Member

    Interesting question. You can run sp_revokedbaccess, which drops a user from a database, but there is no sp_denydbaccess which would prevent a user from being granted access through other permissions.
    I guess the only way is to be very strict about which logins and groups are given dbaccess.
    Adriaan, Apr 18, 2008
    #6

  6. sonnysingh Member

    Thanks Guys... But It is pity though no interface to implement such security measures. I think that an interface must include in future version of SQL Server where DBA can able to restrict the users via IP addresses and visible only to sysadmin. However, if any other way please share it...
    Thanks again...
    sonny

    sonnysingh, Apr 22, 2008
    #7

  7. satya Moderator

    Yes, such an option is not availabel on SQL Server side and rather you have to control such IP Address restriction from FIREWALL & network related APIs.
    satya, Apr 22, 2008
    #8

  8. jkyada New Member

    Hi,
    I guess this may work,
    You can deny permission to specific user by going to server's Property and then clicking permission in left pane and click on specific user and deny the view server state check box.
    Thx.
    Jignesh Kyada.
    jkyada, Aug 1, 2009
    #9

Share This Page