2

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

Thanks

3 Answers 3

1

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.

0

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

0

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .