is this possible to disable remote access for one user or database in SQL Server 2008?
Server Fault is a question and answer site for system and network administrators. It only takes a minute to sign up.Sign up to join this community
The question is quite vague, but I think that you're looking for denying permissions. Right click on the login in question (in the database), and grant him deny_datareader and deny_datawriter roles.
There is always a need to restrict the user logins into the SQL Server databases. Many DBA’s face this difficulty, as the restriction of logins has to be done for different users with different requirements. SQL Server 2005 introduces an option called “LOGON” triggers which is an easy way restrict the number of user logins as per our requirements.
This Logon trigger is created directly on the database server and registered on the master database. The below sample demonstrates the use of login triggers to restrict the user “john” from accessing the database using “SQL Query Analyzer” window.
USE master GO CREATE TRIGGER trgRestrictUser ON ALL SERVER WITH EXECUTE AS 'sa' FOR LOGON AS BEGIN IF (ORIGINAL_LOGIN()= 'john' AND APP_NAME() = 'Microsoft SQL Server Management Studio - Query') ROLLBACK; END;
This logon trigger can be used for various auditing purposes in SQL Server. This is a new feature introduced in the SQL Sever 2005 Service Pack 2.We need to upgrade to SP2 to use this feature.
I'd deny the NT Account Connect Permissions:
USE [master] GO DENY CONNECT SQL TO [DOMAIN\ACCOUNT] GO
Or deny the SQL Account Connecct Permissions:
USE [master] GO DENY CONNECT SQL TO [SQL_ACCOUNT] GO