Menu
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.
706
Question: Is it possible to disable a user on a database or do they have to be removed totally? Answer: You don't need to remove a database user totally to disable them. A database user is associated with a SQL Server Login. So to disable a database user would requires a step to disable the login.
In SQL Server Configuration Manager, in the left pane, select SQL Server Services. In the results pane, right-click SQL Server (MSSQLServer) or a named instance, and then select Start, Stop, Pause, Resume, or Restart.
Using SQL Server Management StudioIn Object Explorer, right-click a server and select Properties. Select the Connections node. Under Remote server connections, select or clear the Allow remote connections to this server check box.
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.
102
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
