User management is very important in our database systems. We prefer DBAs to create or use more windows authentication instead of sql authentication. But conditions may not always be what we want. Today, many applications use sql authentication to access the database. These SQL accounts are easily accessed via SSMS (SQL Server Management Studio), and anyone with sql user information will have access to the database. To avoid this, we will write a trigger.
We will block database access through Management Studio with the user accounts we set in this trigger. Users will receive an error message via Management Studio when they try to login with these accounts.
CREATE TRIGGER [DISABLE_LOGON_APP]
ON ALL SERVER
DECLARE @program_name nvarchar(128)
DECLARE @host_name nvarchar(128)
SELECT @program_name = program_name,
@host_name = host_name
FROM sys.dm_exec_sessions AS c
WHERE c.session_id = @@spid
IF ORIGINAL_LOGIN() IN('sqluser1','sqluser2','testuser')
AND @program_name LIKE '%Management%Studio%'
RAISERROR('This login is for application use only.',16,1)