Configure Access Rights in SQL Server 2005 on Rows in Table

HelpdeskJBC
HelpdeskJBC used Ask the Experts™
on
We have an  Windows Server 2008 R2. On that we installed Microsoft SQL Server 2008 R2, which holds a Database Compatibility level: SQL Server 2005 (90).

We need to restrict the access rights on a table. However, only on certain rows, which have a defined indexname so that only a AD group can access those indexnames!

Is that possible and how?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
you can create a VIEW and define permissions on that VIEW instead of defining permissions for the table itself
Create and indexed view and restrict the access on it.

Author

Commented:
When I apply those permissions on that view does that apply then for all users accessing the table?

How do I change the permissions on the view?
Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

Topic Advisor, Page Editor
Distinguished Expert 2018
Commented:
Doesnt have to be an indexed view...

If you create a view with a built in "where" clause to check the condition - maybe a specific group of users, or whatever security groupings you need to check - maybe individual users depending on how they connect. But that basically gives you read access (you can update via views but best not to).

Anyway, a more comprehensive discussion / white paper : http://technet.microsoft.com/en-au/library/cc966395.aspx says 2005, but still applies and is good background

There is also a "toolkit" that used to be floating around. Havent seen much of it lately, but was pretty good : http://blogs.msdn.com/b/publicsector/archive/2006/03/13/sql-server-2005-tools-for-row-and-cell-level-security.aspx

Unfortunately, there is nothing automatically built in that I am aware of just yet...

Author

Commented:
A good documentation though how do I get that tool without an msdn subscription since I can't get it with only my live.com login account.
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
Not on MSD either any more.

Sorry about that, should have checked first - it was quite a while back... But the Article does cover it all reasonably well, and in reality, your own business model is likely to require a specific approach (did for me anyway, and couldnt really use the toolkit except for a few ideas now covered off by the article).

Author

Commented:
What we have:
- SQL Server 2008 R2
- SQL 2005 (90 compatible) Database
- We access this database through ODBC with an AD group (automatic script login so those users aren't involved in the log in). (That AD group is on our SQL Server in the SQL Management Console under Security\Logins configured and then through 'User Mapping' assigned to a Database as db_owner.)

Our problem is that we want to restrict the access of 3 tables to certain User(s) (best case scenario AD-Group which have unrestricted access to that database) those can access certain rows which need to be defined (Index names). The other AD group should access all other indexe names just fine.

Please advise which way would be the best and if psosible with scripts or what is needed for the implimitation of that.

Thanks in advance.
Thanks all of you I got the answer now had to work it out and needed because of that that long.

For other users which as well want a detailed explanation. Here what I did:

1.) SQL Management Studio: Security>Logins
 a\ Create a 'SQL Server authentication' UserA, which get access to the whole data BUT the restricted data
 b\ Create a 'SQL Server authentication' UserB, which only get access to this restricted data.

2.) Create a Table with all these Indexes that should be restricted.
In my case I created table 'B' with one column 'IndexName' (same name as the primary key of other table 'A') make that 'IndexName' a primary key.

3.) SQL Management Studio: Databases>%DatabaseName%>Views
Create a View, which only shows the restricted IndexName(s) of table 'A'. That means you need to insert table 'A' and table 'B' in this view and 'inner join' them if they aren't already.

4.) SQL Management Studio: Databases>%DatabaseName%>Programmability>Stored Procedures
Create a stored Proceedure 'A' & 'B' as attached in the Code Snippet.

5.) SQL Management Studio: Databases>%DatabaseName%>Programmability>Stored Procedures
Set permissions to activate the restriction.
 a\ Under properties for 'proc_A' there under permissions 'search' choose UserA and grant execute.
 b\ Under properties for 'proc_B' there under permissions 'search' choose UserB and grant execute.

Et voila each user can now see their own data.
In our case (edited version):

---stored Procedure A: all data but restricted data---
USE [%DatabaseName%]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[proc_A]

AS

    BEGIN

        SELECT      *
        FROM        TableA LEFT OUTER JOIN
                    TableB ON TableA.IndexName = TableB.IndexName
        WHERE       TableB.IndexName IS NULL
                          
    END
---end stored Procedure A: all data but restricted data---




---stored Procedure B: only restricted data---
USE [%DatabaseName%]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[proc_B]

AS

    BEGIN

        SELECT       *
        FROM         Only_Restricted_Data_View_Name
                        
    END

---end stored Procedure B: only restricted data---

Open in new window

Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
>> Please advise which way would be the best and if psosible with scripts or what is needed for the implimitation of that.

I am so sorry, I didnt see your posting...

And maybe just as well, because your posting above is terrific. Great piece of work. Well done.

Author

Commented:
I didn't use all the points since I had to get over many days the data by myself. However, mark_wills solution pointed me into the right direction and was most accurate. Though for others I made an easy to follow comment so it should be easier for them then.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial