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).