Using a Logon Trigger to restrict access
A number of our internal applications connect to our production databases for data cleansing operations. Unfortunately, the developers saw fit to have the server, login & password details in a plain text configuration file. This allows the team who use the cleansing tools all the information they need to access my production servers.
I was toying with application roles as a mechanism to get round this issue but I found them a little cumbersome to implement. I spoke with Development to change the way we store these passwords and how we authenticate but it will take a while for the changes to happen. Fine, I’m happy.
In the meantime, I have created a Logon Trigger to restrict users who connect from Management Studio. It’s a pretty simple process. Check out this piece of code:
USE master
GO
CREATE TRIGGER trgManStudioRestrict
ON ALL SERVER WITH EXECUTE AS ‘sa’
FOR LOGON
AS
BEGIN
IF (ORIGINAL_LOGIN() IN (‘User1′, ‘User2′, ‘User’3′) AND APP_NAME() = ‘Microsoft SQL Server Management Studio – Query’)
ROLLBACK;
END;
This works a treat for me.