Hi, I have a table with 4000 records and 4 users work in 1000 records each, now my question is in sql server 2000 can we give permission for the users to work only in that 1000 and they cannot access other records...is it possible. thanks sathish
SQL Server does not implement row level permissions directly. If you want to limit the rows, to which an user has access, you will have to not allow query the table directly and instead of this create a stored procedures and/or functions which will be used to retrieve and manipulate the data according the user. You can create a configuration table for the row level security and according that table you will provide results and or modifications to the data. Option could be also use of InsteadOf triggers to handle the row level security, but I would be careful before going this way. You can take inspiration in the technet article although it is related to the SQL Server 2005. [Implementing Row- and Cell-Level Security in Classified Databases Using SQL Server 2005] :
One mechanism you can use, assuming their login is in the data, you could create a view that filters on the user's login as a parameter, that you either pass it from the app or pull from the USER_NAME function. It's likely to lead to a table scan since with such a limited number of users you'll be access 1/4 of the data at a shot, but assuming there are other selection criteria you might have an index that leads to seeks.
I would like to add to the answers above because the question is about the SQL Server 2000 which unfortunately does not have table valued functions and the views do not accept any parameters. If the situation is as simple as it is described in the question then you can consider the following options: If users connect to the database with Windows authentication then **system_user** can be queried to figure the user's name. This way, you can ensure that neither of these 4 users has a **select permission** to the table and create a view which will serve only allowed records to each user. Something like this: create view dbo.YourTableSubsetView as select * from dbo.YourTable where RecordID = 3001 and system_user = 'your_domain\user4' go Even better option would be to add another column to the table in question. Something like alter table dbo.YourTable add AvailableTo varchar(50) null -- and then update dbo.YourTable set AvailableTo = 'your_domain\user1' where RecordID = 3001 alter table dbo.YourTable alter column AvailableTo varchar(50) not null This way you can create the view like this: create view dbo.YourTableSubsetView as select * from dbo.YourTable where AvailableTo = system_user go Once the view is created you can **grant select on dbo.YourTableSubsetView** to each of the 4 users. Either of the 2 ways described above is used, the view will furnish only those records to the logged in user which the latter is allowed to view/modify. Since the view can safely map each record it returns to a single record in the base table, this view will be updateable even without instead of trigger, and each user will be able to update only own records. If all users connect to the database using a common connection string so there is no way to identify the user without passing a parameter then you can delegate this work to the front end and instead of creating one view, create 4 views (one for each user) and grant select permission to each of the view to only one appropriate user. This will allow the same results, but will be a bit more difficult to implement. Oleg
I just wanted to add an option in here... Why don't you just create some updatable views, and specify them `WITH CHECK OPTION` - and then give permissions on those views, and not the underlying tables. From [this MSDN page]: >**CHECK OPTION** > >Forces all data modification statements executed against the view to follow the criteria set within select_statement. When a row is modified through a view, the WITH CHECK OPTION makes sure the data remains visible through the view after the modification is committed. This basically means that the user with permission to view A gets to see the rows that view A allows, but doesn't allow those rows to be updated such that they no longer appear in view A. For example - you have views split so that view A only sees records with `magicNumber = 1` - if `WITH CHECK OPTION` was specified on the view then you would not be able to update a record using the view and set `magicNumber` to 2 (which would then mean the row was no longer included in the view). :