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...
