How to disable SQL Server Management Studio for a

Question

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.

Answer 1

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.

Answer 2

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

Answer 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.

Answer 4

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

Answer 5

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)

Answer 6

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.