• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 662
  • Last Modified:

Specific Read-Only rows in a SQL Server 2005 Table

Is it possible to make specific rows read-only in a table?
0
mcgitsupport
Asked:
mcgitsupport
  • 4
  • 2
  • 2
1 Solution
 
wael_tahonCommented:
I think you can achieve this in your application layer.
lets say that all records that you want to make readonly will have a column called "IsLocked" equal to "1" and in your grid you will disable the edit control based on the value of "IsLocked" column.
0
 
mcgitsupportAuthor Commented:
Is it possible to achieve this on the sql server side without changing the code?
Thanks..
0
 
dportasCommented:
Yes. You can deny access to the table and implement the row-level security in a stored procedure (assuming you perform all the relevant updates through a stored procedure).
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
mcgitsupportAuthor Commented:
Create a view on the table and give only Select rights on that view.
0
 
mcgitsupportAuthor Commented:
The scenario is that I have a table in SQL Server which is composed of data that changes every day.  So I have to store each days worth of data.  The problem is that I don't want anybody else to be changing a certain part of the existing data via an application,query or stored procedure, so I want to make certain rows read-only.  For example, from 01/01/07 to 12/31/07 all rows should be read-only.  The rest should be editable since we are in a new year.
In addition I need to keep all data in the same table so that I can run reports of the existing data.

Basically is there a function I can call within SQL Server to make certain rows to be read-only?
0
 
dportasCommented:
mcgitsupport: Whether you use views or procs to achieve this, the prerequisite is that you manage secuirty via logins, user roles and GRANT/DENY. If you are able to do that then you can achieve what you have asked for.

If you do not or cannot control security in your database then no "read-only" function is going to help. Even if such a feature existed it could presumably be turned on and off unless you restricted the rights of other users to enable or disable it at will. I hope you can see why "access control" is the right answer to your question - not "make rows read only".
0
 
wael_tahonCommented:
dportas: I do agree with, there are no way to control it if you can't control your DB access and the only way is through your application logic
0
 
mcgitsupportAuthor Commented:
We ended up using a trigger that rollbacks the insert/update transaction if the date_processed column in the row is less than the particular date.
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 4
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now