Log Shipping – NORECOVERY vs. STANDBY mode

The following logon trigger will restrict a user from connecting thru SQL Server management studio. As always, test this first before implementing.

This will deny all the connections made by the user ‘test’  from the application ‘ssms’

 

CREATE TRIGGER ltr_restrictUserFromSSMS
ON ALL SERVER 
FOR LOGON
AS
BEGIN
IF
(PROGRAM_NAME() LIKE 'Microsoft SQL Server Management Studio%' AND ORIGINAL_LOGIN() ='test' )
BEGIN
ROLLBACK;
END
END


DROP TRIGGER ltr_restrictUserFromSSMS ON ALL SERVER

 

 

 

Sometimes the connections from SSMS will listed as ‘Microsoft SQL Server Management Studio –query’; so if you want to restrict all the connections made from ssms and the queries run from them, it is good to use   PROGRAM_NAME() LIKE 'Microsoft SQL Server Management Studio%'