How to disable SQL Server Management Studio for a

问题:

Is there a way to prevent users from getting into SQL Server Management Studio so that they can't just edit table rows manually? They still need to access the tables by running my application.

回答1:

You DO NOT need to worry about them having access to the tool. Simply make sure they do not know any of the SQL logins for the specific Databases that have read/write permissions, if they do, change the password. If they have access to the DB via Windows Authentication, make sure that they are in a datareader role. You can use roles to manage what the users can do in SQL.

回答2:

You can use the DENY VIEW ANY DATABASE command for the particular user(s). This is a new feature available in SQL Server 2008.

It prevents the user from seeing the system catalog (sys.databases, sys.sysdatabases, etc.) and therefore makes the DB invisible to them in SQL Management Studio (SSMS).

Run this command from the Master Database:

DENY VIEW ANY DATABASE TO 'loginName'

The user is still able to access the database through your application. However, if they log in through SSMS, your database will not show up in the list of databases and if they open a query window, your database will not appear in the dropdown.

However, this is not fool-proof. If the user is smart enough to run the Query Command:

USE <YourDatabaseName>

Then they will see the database in the Query Analyzer.

Since this solution is taking you 90% there, I would give the database some obscure name not let the users know the name of the database.

回答3:

I would suggest you lock down the database and give appropriate read-only (or other) rights to the user. That way the user can still use management studio to run select queries and such.

If you don't want the user to have any rights at all then you could do that as well.

回答4:

You can use a trigger.

CREATE TRIGGER [TR_LOGON_APP]
ON ALL SERVER 
FOR LOGON
AS
BEGIN

   DECLARE @program_name nvarchar(128)
   DECLARE @host_name nvarchar(128)

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


   IF ORIGINAL_LOGIN() IN('YOUR_APP_LOGIN_NAME') 
      AND @program_name LIKE '%Management%Studio%' 
   BEGIN
      RAISERROR('This login is for application use only.',16,1)
      ROLLBACK;
   END
END;

https://www.sqlservercentral.com/Forums/1236514/How-to-prevent-user-login-to-SQL-Management-Studio-#bm1236562

回答5:

If your application is running as a service/user account then only that account requires access to the database. The individual users' account do not require any access to the database and therefore they won't even have read access. Your app will be the gateway to the data.

If the users are running the application under their user accounts then grant them read-only permission. You can simply add them to the db_datareader role.

Hope this helps!

回答6:

If your application only used stored procedures to modify the data, you could give the end users access to run the stored procs, but deny them access to modify the tables.

回答7:

• Don't let them know what the database login is.
• If you can't restrict the login, use stored procedures exclusively for updates and disable any CREATE,DELETE,INSERT, or UPDATE permissions for that user.

回答8:

An Application Role will allow you to secure database objects to your application instead of the logged on user.

回答9:

You can deny 'Users' access rights to the ssms.exe executable file, while granting the relevant users/administrators rights to it.

回答10:

I agree with Jon Erickson as a general rule

• do not allow any users access to the tables, but only allow access through stored procs
• do not allow general user accounts access to stored procs, but only the account your app runs under (whether it's an integrated login or SQL login)

回答11:

Make well usage of Database Roles, if Users should only have SELECT (read) access assign them the db_datareader Role. Even if they login using SSMS they will can execute only SELECT statements.