SQL Server Permissions – Create a User Account Login That Cannot Login from SSMS

permissionssql serversql-server-2008ssms

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.

Best Answer

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.

Related Solutions

Sql-server – SQL Server Management Studio Change User Server Roles to sysadmin

You've explained that you don't have the SA password and you're not an administrator. In that case, you're going to have to hack your way around it.

See Think Your Windows Administrators Don’t Have Access to SQL Server 2008 by Default? Think Again. by Argenis Fernandez.

That post explains how to impersonate the NT AUTHORITY\SYSTEM login using PsExec (or creating a Windows Scheduled Task running under the SYSTEM account) to create a new login for yourself. Then, you can log in via that new login, and grant your Windows account SA permissions.

SQL Server – How to Change Default Security for Database Access

You could create a domain group that you could use to create as login and user on your SQL Server and YourDatabase. After creating the domain group, you could do something like:

USE [master]
GO
CREATE LOGIN [DOMAIN\AllUsersForYourDatabase] FROM WINDOWS
GO
USE [YourDatabase]
GO
CREATE USER [DOMAIN\AllUsersForYourDatabase] 
FOR LOGIN [DOMAIN\AllUsersForYourDatabase] 
GO

You can then make the logins on your domain members of that group. All the logins will inherit the permissions granted to the group that gives them access. (You could include Domain Users in the group if you truly wanted everyone in the domain to have access.)

Of course, you need to grant the needed rights to the YourDatabase user [DOMAIN\AllUsersForYourDatabase] so that everybody in the group gets the same rights.

If you need some users to have additional rights, those can be individually granted. Or you can create another Group that would include only the users with the extra rights. The users will get the aggregate of all rights from all groups for that login.

Best Answer

Related Solutions

Sql-server – SQL Server Management Studio Change User Server Roles to sysadmin

SQL Server – How to Change Default Security for Database Access

Related Question