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

  2. jagblue New Member

    Use sp_droplogin will drop current login to use sqlserver
    you can find more help on BOL
  3. sonnysingh Member

    Thanks .. but I do not want to drop the login as other user using this logins....
  4. ghemant Moderator

    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.
  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.
  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...

  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.
  8. jkyada New Member

    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.
    Jignesh Kyada.

Share This Page