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.

    Requires Free Membership to View

    Login

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.

 

For More Information

This was first published in September 2003

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top