Row level permissions

Does SQL Server 2000 have support for row level permissions?
DenisDenis,
not directly, but this can be mimiced by using view based security and the
with check option.
Regards,
Paul Ibison|||Hi,
Row level security can not be set directly, Have a look inthe below article.
es.htm" target="_blank">http://vyaskn.tripod.com/ row_level...as
es.htm
Thanks
Hari
MCDBA
"Denis Crotty" <anonymous@.discussions.microsoft.com> wrote in message
news:A669D750-7E3F-473A-8A86-F17D1C222A3C@.microsoft.com...
> Does SQL Server 2000 have support for row level permissions?
> Denis|||Wow. The database I'm designing is fairly large I think, 40+ tables. So I'
m looking at adding a user column for every table and then creating views an
d stored procedures for every type of access to these tables? Or am I not u
nderstanding the article?
Denis|||Can you point me to a good reference?
Denis|||Denis,
Hari's (Vyas's) reference is a good one. If you want to relate the
SUSER_SNAME() function to something business related (rather than creating
columns on each table with usernames), you could use a UDF in the view which
takes the SESER_SNAME() scalar value as an argument. Using a UDF and another
table will also allow a one-to-many relationship to be established (eg if
user JoeBloggs maps to two cities and you want a view on a sales table which
needs to be partitioned according to user and city).
HTH,
Paul Ibison|||Denis,
please see my reply above.
HTH,
Paul Ibison|||Thanks for the reply,
I'm sorry, it must be like talking to a wall but I'm not really understandin
g what you mean. What is SESER_SNAME()?
The database I have is functionally like the article described. I have mult
iple users submitting the same type of data, but when they later want to vie
w it they are not allowed to see anyones but their own. They want it set up
centralized, because noone
wants to have to hire a DBA. The data they enter gets placed in the appropr
iate tables, as I mentioned there are 40+ table (does that constitute a larg
e database structure?). I'm unclear whether we are talking about creating a
view for every table alon
g with SP's for inserts, updates and deletes, or if I'm misunderstanding.
Sorry, if you can help me clear this up it would be greatly appreciated,
Denis|||Denis,
SUSER_SNAME() returns the login which can be mapped to a column in each
table. Yes, you'll need a view on each table if you need row-level security
on each table. However, you don't necessarily need an extra column on the
table which is populated with loginnames. This is the easiest way, but if
you use a UDF, you can transform the login into something business related.
The view in this case would be
select col1, col2...
from table1
where colx = dbo.myudf(suser_sname())
As far as the sps are concerned, you could code in a check to suser_sname()
to verify that the user can do the appropriate action, or more simply just
access the view from the stored procedure. If the view is created using WITH
CHECK, they can't do an invalid insert. Updates and deletes will only apply
to rows accessed through the view, which must be valid anyway.
HTH,
Paul Ibison|||Thanks Paul,
Your help saved me hours of redesign down the road.
Denis