The concept of read only vs read/write files has been around for nearly as long as computers have been used in business. Most SQL Server DBAs probably know that you can have a read only database for those situations where you don’t want anything changed, like an archived set of data. But what about setting a table to read only? This article will explore two methods to achieve this.

The Easy Way

Most people that want to set something to read only usually want to prevent most people from changing things, but they want to be able to make a change themselves. In SQL Server, tables don’t have a specific “read-only” setting, but you can ensure they are read only by granting SELECT permissions on the table with no other permissions. This means that as you set up your roles and make use of the fixed database roles that are built into SQL Server, you need to be careful of permissions granted to those roles. In general, you will not want to grant INSERT/UPDATE/DELETE permissions to any role that does not need to change the table.

If you find yourself in a situation where you want a group of people to have read-only access, but they are members of other roles that have access, you can easily solve this issue as well. Create a new role for the read-only group and DENY permissions for INSERT, UPDATE, and DELETE on the table. This will override the permissions granted from other roles.

The nice thing about this approach is that some users see the table as read-only and others can make changes. However, since the user does not have permissions, errors are reported to the calling application. If the application doesn’t handle these errors well, there could be issues with this method.

The Hard Way

Another way to make a table read-only is by setting up a trigger on the table that fires for the INSERT, UPDATE, and DELETE events and rolls back any changes. This can be done by creating a trigger that rolls back the transaction:

create trigger trCustomers
on Customers
for insert, update, delete
as
rollback transaction

This trigger will allow a user to make a change, immediately undo the change by rolling back the implicit transaction, and report success to the user. However, no one can make changes to the table without disabling or removing the trigger.

One downside of this method is that if you have users who are unaware of its implementation, their data changes will not be recorded, which may lead to confusion or support calls.

Conclusion

Setting a table to read only isn’t a complicated process, but the two methods each have their advantages and disadvantages. The first method, using permissions, is the best way to handle things in most cases. However, if you do not want errors returned to users, then the second method of using a trigger may work better in your environment.

One last variation on the read-only table is the logging table. Often, once something is logged for troubleshooting, tracking, or regulatory purposes, you do not want it changed or possibly even read. In this case, you might want to also add a trigger to prevent updates or deletes and ensure that your data is protected.

Let us know what you think of these methods or share your own way of handling this situation using the “Your Opinion” button below.

©2006 dkranch.net Steve Jones