Q
Problem solve Get help with specific problems with your technologies, process and projects.

Implementing row-level security

How do you implement data/row-level security in SQL Server 7.0 or 2000? I have a data warehouse with data from various organizations. I want to restrict organization data based on the users logging into the SQL Server database via Crystal Enterprise 9.0 and Crystal Report Designer 9.0.
There is no way to do so with built in SQL Server security. You can implement this functionality yourself. To do this, you would need the combination of views, a table of users/groups, and the suser_sname() function. Essentially, you set up your tables where you include a column to contain the group ID of a set of users, which you create. You then create a view over the top of your base tables which combine your base table with a security table on the security group ID. This view utilizes the suser_sname() function to pass the ID of the user logging in as part of the where clause in the view. This will restrict access to rows of data based on your custom security requirements. This requires that users login with their own credentials and not generic accounts.
This was last published in September 2003

Dig Deeper on Microsoft SQL Server Installation

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close