Specific Read-Only rows in a SQL Server 2005 Table

Is it possible to make specific rows read-only in a table?
mcgitsupportAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.