Sql-server – Methods of Denying login by specific SQL Authenticated user using Management Studio

permissionssql serversql server 2014

I am trying to create a way of restricting/complicating access, for a particular user, to login to SQL Server, using Management Studio with SQL Server Authentication.
I would like a way that also gives the user a custom error whenever he tries to connect.
One way I know of is to use Logon triggers. Ex:

CREATE TRIGGER ManagementStudio_Connection_Limit_Trigger
ON ALL SERVER WITH EXECUTE AS 'TestUser'
FOR LOGON
AS
BEGIN
    IF ORIGINAL_LOGIN()= 'TestUser' AND EXISTS (SELECT * FROM sys.dm_exec_sessions WHERE session_id = @@SPID AND program_name LIKE 'Microsoft SQL Server Management Studio%') 
        ROLLBACK;
END;

(I know that the Application Name can be spoofed but if they really want to connect, that is OK)

Are there any other ideas?

Edit: The fully story is that the SQL Authenticated user in question, is a user used primarily from some Java applications. I cannot change the permissions without a full test of all applications. Also, unfortunately the Java developers knows these passwords and uses this user willy-nilly. So, if we change the password, the developers need to know. Windows Authentication from Java is a can of worms I'd prefer not to go into right now. And again, if they manage to find a way to connect anyway, it's not the end of the world. I just want to make it complicated enough for them to reconsider. (They have personal Windows Authenticated users which they can use and sometimes do).

Best Answer

Connecting to management studio should not be that threat to you unless and until that user gets full read/write on the database in question which needs to be secured.

You can use DENY VIEW ANY DATABASE Permission herewhich will make the DB sort of invisible for that user when he tries to connect via SSMS, however if they are smart enough and hit a query with USE <database name> they can proceed.

Else, its better you just give them access via windows authentication (with controlled access) and do not share any SQL login credentials which have the permission to execute the task which you do not want the user to.

Also, read this thread

Related Question