Hi,
Are there any new feature for restricting users access to particular rows in
SQL Server 2005?
Thanks in advance,
Leila

Leila
I have not used it by myself if I remember well there is an option that
allows you to encrypt a row or a column.





Show quoteHide quote

Go to:
http://support.microsoft.com/search/?adv=1
and for "Search Product" select "SQL Server". In the "For" box enter
"row-level security". In the "Categories" select and unselect the options
according to your needs and such.

Does that help?


Show quoteHide quote

Consider adding a user name column to the table and then using the
SYSTEM_USER function in a view of the table. Only allow users to access the
view, not the table.

E.g.:
Table definition:
Col1 : Col2 : ... : UserName (default=system_user)

View definition:
select Col1, Col2, ...
         from <table>
         where (UserName = system_user)

This way each user only sees his/her own rows.


ML

---
http://milambda.blogspot.com/

Use VIEWS that have a  WITH CHECK OPTION.

Please see http://www.technicalmedia.com for row level security extension to
SQL 2005 (free eval, $100 dev license, no runtimes)

Row-Level Security for Microsoft SQL Server 2005
=============================================
Data Nomad® is an affordable set of developer tools that extend the
Microsoft SQL Server 2005 platform to provide row-level security and remote
access features allowing developers to accurately and efficiently create and
manage powerful distributed applications that insure access to information is
protected.

Developers of .NET 1.1 and .NET 2.0 smart client and web applications can
now easily add row-level security to database applications through the Data
Nomad® developer tools.  Existing databases are easily configured by
identifying the tables to be protected and by creating row-level permission
grants. 

The same (unmodified) SQL statements work against the Nomad database
extensions.  The extended database appears to only contain the rows to which
the user has at least read permissions.  Database updates and deletes only
succeed against rows to which the user has owner permissions.

This type of seamless integration is achieved by leveraging two powerful new
features of Microsoft SQL Server 2005: the schema (a collection of database
objects that form a single namespace) and the synonym (an alternative name
for another database object providing a layer of abstraction over the
original object).

The Nomad extensions support both SQL Server authentication and Integrated
NT authentication for database connections, and support local, LAN-connected,
and Web-connected backend databases.


Show quoteHide quote

Evaluation of conditions
complex and large triggers
Apostrophes and Dynamic SQL
Table design question on LARGE table
Query help