SQLTeam.com | Weblogs | Forums

Disable manual editing of data in Microsoft SQL Server

I am using Microsoft SQL Server. As you know, edit the data in the database either manually by the original environment using SQL Server user sa or any user of other authorized and also through programming environment that connect to SQL Server to edit the data. Is there a way to disable this editing of data in the SQL Server environment so there is no person in the database manually does not change?

No users even through mssql unable to make changes in the original data. For example, to manually stock the number to another number that this event would not have realized how to change.

Don't let the users have access to SSMS. Period.

The DBA, who is the one one/group to have access to SSMS, is assumed to know not to go messing about like that, or to only do it in situations where it is warranted. (Here "warranted" is "absolutely never", but I can imagine that might not work for the whole world over!)

Doesn't solve the SSMS user-access problem, but creating VIEWs for each table, which have select-only permissions, and then having no SELECT permissions on the tables themselves (for the users) is a way of providing a sandpit environment for users to access the data. Any APP that needs to select/update the tables needs a different permission-route to the one that the users view the data with, via the VIEWs.

For example, you could hook up Access to the VIEWs as a sandpit-replacement for SSMS

can SINGLE_USER help?
Can wizard sql manager for remote did not open ?
Who does not have access to sql server console? And changes only through a interface software or Command Line in master database server operating system?
I think that best way is "database only is edited by sa when connect directly to server and use command for edit database on shell environment and nobody cannot remotely edit it, i means dont connect console "ssms" remotely."

One way is to create an Application Role which has DML permissions and all other users have db_datareader only. Another way is to create a login and grant impersonation on it to the person creating stored procedures and create them with execute as the given login. Give that login DML permissions but everyone else db_datareader. This way, changes to data are only made executing stored procedures and data changes are prevented Ad-Hoc.

1 Like

Why do the users have permissions to modify data in the first place? Instead, grant them authority to exec needed stored procs, but explicitly DENY them authority to modify data (db_denydatawriter).

And for the love of security, disable the "SA" login! It's the first step in making any server secure. The second step is to remove sysadmin privs from all users and applications. Only highly trusted DBAs (which might not be all of them) should have sysadmin privs.

And, no... you can't prevent anyone with high enough privs from making modifications. Even if you wrote "instead of" triggers for every table, someone with the right privs could temporarily disable the trigger, do their deed, and reenable the trigger.

1 Like