|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Readonly Table ?In SQL Server 2005 is it possible to set a table as readonly? I know you
can mark the entire database as readonly but what about just on table? Am Thu, 10 Aug 2006 11:29:37 -0400 schrieb Jay:
> In SQL Server 2005 is it possible to set a table as readonly? I know you You can remove grants for insert/update/delete on this table.> can mark the entire database as readonly but what about just on table? bye, Helmut You can put the table on a file that is configured to be read only.
Adi Jay wrote: Show quote > In SQL Server 2005 is it possible to set a table as readonly? I know you > can mark the entire database as readonly but what about just on table? You can provide only SELECT permissions, and DENY UPDATE, DELETE.
GRANT SELECT ON dbo.MyTable TO UserRole DENY UPDATE, DELETE ON dbo.MyTable TO UserRole -- Show quoteArnie Rowland, Ph.D. Westwood Consulting, Inc Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous "Jay" <msnews.microsoft.com> wrote in message news:OJbvKHJvGHA.4972@TK2MSFTNGP05.phx.gbl... > In SQL Server 2005 is it possible to set a table as readonly? I know you > can mark the entire database as readonly but what about just on table? > > Jay wrote:
> In SQL Server 2005 is it possible to set a table as readonly? I know you Do it with permissions - DENY update and insert rights to the necessary > can mark the entire database as readonly but what about just on table? > > users. Jay (msnews.microsoft.com) writes:
> In SQL Server 2005 is it possible to set a table as readonly? I know you CREATE TRIGGER readonly_tri ON tbl INSTEAD OF INSERT, UPDATE, DELETE AS> can mark the entire database as readonly but what about just on table? RAISERROR('Hands off! This table is readonly!, 16, 1) ROLLBACK TRANSACTION RETURN On SQL 2005 I believe you can set a filegroup as read-only. Thus you could create filegroup for the table. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx >> CREATE TRIGGER readonly_tri ON tbl INSTEAD OF INSERT, UPDATE, DELETE AS One shortcoming with the trigger approach is that TRUNCATE and the ALTER >> .. DDLs will bypass it. -- Anith Anith Sen (an***@bizdatasolutions.com) writes:
>>> CREATE TRIGGER readonly_tri ON tbl INSTEAD OF INSERT, UPDATE, DELETE AS Good point. But you can counter that by setting up a foreign key>>> .. > > One shortcoming with the trigger approach is that TRUNCATE and the ALTER > DDLs will bypass it. that refers to the table. The permissions stuff that was suggested by several does not stop an administrator. Of course, an admin can get around a trigger too, but at least the trigger prevents him from changing the table by mistake. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Thanks for all the replies. What I need to do is prevent anyone (even
admins) from altering the data in the table. Looks like my best approach maight be a readonly filegroup. Show quote "Erland Sommarskog" <esq***@sommarskog.se> wrote in message news:Xns981C3B7BBB0BYazorman@127.0.0.1... > Anith Sen (an***@bizdatasolutions.com) writes: >>>> CREATE TRIGGER readonly_tri ON tbl INSTEAD OF INSERT, UPDATE, DELETE AS >>>> .. >> >> One shortcoming with the trigger approach is that TRUNCATE and the ALTER >> DDLs will bypass it. > > Good point. But you can counter that by setting up a foreign key > that refers to the table. > > The permissions stuff that was suggested by several does not stop an > administrator. > > Of course, an admin can get around a trigger too, but at least the trigger > prevents him from changing the table by mistake. > > > > -- > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Jay wrote:
> In SQL Server 2005 is it possible to set a table as readonly? I know you move your table to a read-only file group.> can mark the entire database as readonly but what about just on table? nor DENY, nor TRIGGER does not protect your data from TRUNCATE.  |
|||||||||||||||||||||||
Other interesting topics