We have a situation where Developers do not have any UPDATE permissions, BUT they work with applications and see connection strings -> they know passwords from some SQL accounts (example SQLLogin1) that have UPDATE permissions. Our operations currently are not perfect, and sometimes production data needs to be modified (no GUI for that yet).

Instead of contacting DBA, and asking him to modify the data, Developer would (improperly) use SQL account SQLLogin1 (that has permission to modify the data), and connect over SQL Server Management Studio to modify the data himself.

DBA can not change password for SQLLogin1 without Developer seeing the new connection string and new password, since the application connection string that uses SQLLogin1 is maintained by Developer.


Is there a way to deny access to SQLLogin1 SQL login, but only if it is connecting over SSMS?

At the same time if SQLLogin1 is connecting over .Net SqlClient Data Provider (program_name in the sys.dm_exec_sessions), it must be allowed to login.

This way we want to not let Developer connect over SSMS using SQLLogin1, while the application that is using SQLLogin1, would still be able to connect.


6 Answers 6


You can use a server logon trigger to make custom logon validations and reject them whenever you see fit. You will see this trigger listed below "Server Objects" and inside "Triggers" if you are using SSMS.

For example:

CREATE TRIGGER strRejectSSMSConnectionForSQLLogin1

    IF ORIGINAL_LOGIN() = N'SQLLogin1' AND PROGRAM_NAME() LIKE N'Microsoft SQL Server Management Studio%'
        RAISERROR('Direct connection by SSMS refused.', 16, 1)


The ROLLBACK inside the trigger will reject the connection (there's an implicit transaction wrapping the call to the trigger on the logon event).

Be careful when implementing logon triggers, if not coded properly you will be rejecting logins that should be able to login (including your own!). Make sure to test on test/dev environments first.

Keep in mind that this code is executed before the session is created, so system views that rely on the session id (SPID) won't contain the currently checked login until the triggers ends without rollback or high enough failure.

  • thank you! question - if I make any mistake in logon trigger, and it blocks even sysadmin account from logging in, is there still a way to get into SQL Server and disable logon trigger ? Commented Mar 4, 2019 at 14:09
  • 5
    You can drop the trigger without it firing if you connect with a DAC (dedicated administrator connection). It's a particular single-user connection you can issue against the server whenever things go wrong. It's usually used directly with sqlcmd, but you can do it with SSMS too. learn.microsoft.com/en-us/previous-versions/sql/…
    – EzLo
    Commented Mar 4, 2019 at 14:15
  • 6
    This will work for a few minutes, until the developer uses a different tool. You simply cannot keep a good developer out if they know a login with permissions.
    – Joe
    Commented Mar 4, 2019 at 15:13
  • 5
    This is more an policy solution than a security solution. IE the logon trigger makes it clear that it's against policy to directly connect to the production database. And since it's unlikely you can protect against a actually malicous developer anyway, that may be good enough. Commented Mar 4, 2019 at 15:22
  • 1
    @voo I should have clarified. You can't protect against malicious developers with access to the production environment. Commented Mar 4, 2019 at 18:08

I think there is no reliable solution for your problem since Application Name is modifiable parameter that cam be changed by any user.

Here is how to change it within SSMS:

In Connect to Database Object dialog choose Options, open Additional Connection Parameters and choose any name for Application Name like this:

enter image description here

Now sys.dm_exec_sessions DMV and Program_name() will show you what you passed in your connection string in Application Name parameter:

enter image description here

  1. In the ideal sense, this is a process / policy / management issue. Even if someone knows the password, if it is against company policy for anyone but a DBA to connect to Production (well, you might have a Release Engineering team and/or sys admins, etc), and there are penalties for breaking the rules, then that should be sufficient (assuming that such rules are enforced).

  2. Trying to prevent a particular application from connecting is impossible. As sepupic demonstrated, it is fairly easy to change the "program name". But even if the developer can't figure that out, there are plenty of other programs that can connect to SQL Server. Most people will have access to SQLCMD.exe and even the deprecated OSQL.exe. The developer can connect from within Visual Studio, and they can even create their own app to connect via ".Net SqlClient Data Provider". Oh, and now we even have Azure Data Studio. It's just too many.

  3. Still, this might could still be possible if we approach it from the other direction: instead of preventing application X from connecting, how about only allowing application Y to connect? Sure, we again get into the "program name", and even "hostname" can be spoofed, BUT, I am pretty sure that the client's IP Address cannot be spoofed (at least not through the connection string keywords). You know the IP Address of the app server(s), or can easily find it from the sys.dm_exec_connections DMV (in the client_net_address field).

    Starting with the Logon Trigger suggested by EzLo, we can modify the logic that determines if the connection is valid or not to be the following:

        AND (
                 CONVERT(VARCHAR(10), CONNECTIONPROPERTY('net_transport')) <> 'TCP'
              OR CONVERT(VARCHAR(10), CONNECTIONPROPERTY('client_net_address')) <> ''
     -- uncomment below (and comment-out line above) if app uses multiple IP addresses
     --       OR CONVERT(VARCHAR(10), CONNECTIONPROPERTY('client_net_address'))
     --                   NOT IN ( '', '', ...)
        RAISERROR('Non-application connection refused.', 16, 1);

    The only ways in now would be to either log onto the Production machine, or to have their workstation spoof the IP of the app server. Hopefully devs do not have any access to log onto Production. And spoofing an existing IP on a network causes problems which might adversely affect Production, so they won't be trying that, right? Right?


You can't cut off a specific client, as already detailed in the other answers.

The solution is to remove the access privileges to the production systems from the developer's accounts.

Any change must be scripted and a dba will run the script.

Deployment is performed by a sysadmin; devs produce a package they give to someone with proper privileges and devs never see the configs used on production systems.

Debugging is arranged on a case by case basis with a copy of the production data in a staging environment as a preferred solution or a temporary account with limited privileges if needed.


I previously worked for a company that had this problem with one developer. He was fired, but we also implemented a table that had the LoginName and AllowedMachine (Application Server) via a Login Trigger. This resolved our problems. Or maybe it was due to the firing.


A slight variation on the theme....

Adds a check for SYSADMIN membership


    -- define variables
    DECLARE @program_name nvarchar(128)
    DECLARE @host_name nvarchar(128)

        @program_name = program_name, 
        @host_name = host_name
    FROM sys.dm_exec_sessions AS c
    WHERE c.session_id = @@spid

    AND @program_name LIKE '%Management%Studio%' 
    AND (select SYSADMIN from sys.syslogins where NAME = ORIGINAL_LOGIN()) <> 1
            RAISERROR('Direct Connection by SSMS refused.',16,1)
            --The ROLLBACK inside the trigger will reject the connection (there's an implicit transaction wrapping the call to the trigger on the logon event).
            RAISERROR('Direct Connection by SSMS accepted.',16,1)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.