Giving permission to view CERTAIN records

I've been looking through permissions to see if it's possible to grant a user permission to see only certain records from a table.

For example:

I'm granting users to view records in the table 'Sales', but I only want the users to see their respective data.

User A should only be able to see Sales where Sales.Location = 1

User B should only be able to see Sales where Sales.Location = 2

... and so on.

I believe I've read this is possible, but in looking through the permissions you can define, I've only found where you can limit the columns a user sees, not records.

Anyone have any ideas or did I just remember something wrong?

This cannot be done through permissions today. This topic is known as row-level-security. You can search this forum for old threads on this topic. The following whitepaper may also be useful to you:

http://www.microsoft.com/technet/prodtechnol/sql/2005/multisec.mspx

For your example, one solution could be to use two views that filter the sales data and grant permission on each view to a different user. Users should not be able to access data directly, only through the views they have access to.

Thanks
Laurentiu

|||

Great! This sounds both useful and preferable. Too bad that when the project I'm working on gets under way, they'll likely be a couple hundred different users. Good thing I'm not developing it!

Thanks!

|||

You don't really need a view for each user, you'll need one for each type of user access you need to grant.

Also, the toolkit used by the whitepaper I mentioned is available at http://blogs.msdn.com/publicsector/archive/2006/11/16/sql-server-2005-label-security-toolkit.aspx.

Thanks
Laurentiu

|||

You might try something like this:

create table Account(AccountName sysname, Location int)

insert Account values('mydomain\myuser', 1)

create table Sales(SalesData varchar(20), Location int)

insert Sales values ('xyz', 1)

go

create view vwSales as

select Sales.* from Sales join Account on Sales.Location = Account.Location

where Account.AccountName = suser_sname()

This assumes that the user is logged into SQL Server with their Windows user account. If they are using a service account or a non-Windows account, this won't work.

Ron Rice

|||

In our Marketing Information System, end-users see only views, corresponding to their department and position.

Who needs row level security when you can create views...