Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

Thanks

share|improve this question
add comment

3 Answers 3

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

share|improve this answer
add comment

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.

share|improve this answer
add comment

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
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.