Wednesday, November 5, 2014

Restrict user from connecting to the database from SSMS

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%' 



 



 



image

0 comments:

Post a Comment