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
 

Hi

Use sp_droplogin will drop current login to use sqlserver

you can find more help on BOL

Thanks

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

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         

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.

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
 

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.