Create a user account login that cannot login from SQL Server Management Studio

Question

Can we create a new user that can access the database, but cannot access it via SQL Server Management Studio (SSMS)?

I need this to create a "safe" connection to the database through VBA of Office application, but in case the username and password is leaked, users still cannot connect through the SSMS. I made the user as data reader only, but for peace of mind, I want to make sure users cannot access it via SSMS, so that unwanted viewing of data may be prevented.

I'm using SQL Server 2008 R2. Please kindly help or share if there are other ways to do this.

Answer 1

This is what an Application Role is meant for.

An application role is a database principal that enables an application to run with its own, user-like permissions. You can use application roles to enable access to specific data to only those users who connect through a particular application. Unlike database roles, application roles contain no members and are inactive by default. Application roles work with both authentication modes. Application roles are enabled by using sp_setapprole, which requires a password. Because application roles are a database-level principal, they can access other databases only through permissions granted in those databases to guest. Therefore, any database in which guest has been disabled will be inaccessible to application roles in other databases.