The concept of read only v read/write files has been around for nearly as long as computers have been used in business. And 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.

I had someone query the other day about setting a table to read only. They'd known you could do an entire database, but what about a table? It was an interesting question and a few things came to mind right away, so I thought I'd jot a few notes down and share this with others.

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. Usually this is someone that is administering the file and they mark the file as read-only. Users can access the file, but not change it. If the administrator wants to make a chance, they unmark the read-only flag, make their change, and save it, setting the read-only flag again.

While tables in SQL Server don't have any specific "read-only" setting, there is a simple way to ensure they are read only. It's similar to the ACL permissions that exist in the file system. You just grant them SELECT permissions on the table with no other permissions.

This means that as you setup 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. This means the builtin db_datawriter should not be assigned.

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.

Note: One caveat here. Permissions granted on a column basis are not overridden by DENY. This is a bug that may be corrected by the time you read this, so check this before you implement a role with DENY permissions.

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

There is a second way to make a table read-only, which was the first thing to come to mind for me. Since I've been doing DDL trigger work with SQL Server 2005, this was the first thing I thought of when I was asked the question.

You can setup a trigger on a table that fires for the INSERT, UPDATE, and DELETE events and rolls back any changes. This is simple to do and for a table called Customers, you would compile this:

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

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. If you've ever been the victim of this, it can be really annoying, but it does prevent all changes to the table.

The downside of this approach is that no one can make changes to the table without the trigger being disabled or removed, depending on your version of SQL Server.

There is one other downside of this method. If you have users that are unaware of its implementation, then your phone may ring more often than you'd like because their data changes are not being recorded.

Conclusion

Setting a table to read only isn't a complicated process, but the two methods each have their advantages and disadvantages. Personally I think the first method is the best way to handle things, but if you do not want errors returned to users, then the second way 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. I have seen systems that granted only INSERT permissions or maybe INSERT and SELECT permissions on a table to ensure that users could only add data to it. In this case, you might want to also add a trigger to prevent updates or deletes and ensure that your data is protected.

Let me know what you think of these methods or your own way of handling this situation using the "Your Opinion" button below the the "Join the discussion" link above.

©2006 dkranch.net

Steve Jones