Disable remote access for one user/database in SQL Server 2008

Question

is this possible to disable remote access for one user or database in SQL Server 2008?

Thanks

Answer 1

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.

More ideas here:
http://www.sqlservercentral.com/Forums/Topic490100-359-1.aspx
http://www.sqlservercentral.com/Forums/Topic501874-146-1.aspx

Answer 2

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.

Answer 3

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