In SQL Server 2005, I need to manipulate users' rights at row level. So far, I'm thinking of making a view according to users' needs, and grant permissions on the view. Is there any other possibility in SQL Server 2005?

    Requires Free Membership to View

    Login

As a quick solution, you may have the table in which you ultimately access have an additional character that can be the user login name. Have users select from a view that in turn selects from this table with the following filter: (where clause) where UserLoginName = suser_sname()

This will filter out only that particular user's data. If you want to get more complex, Microsoft has an excellent white paper Implementing Row-and-Cell-Level Security in Classified Databases Using SQL Server 2005 that uses custom database roles to accomplish the same.

This was first published in June 2006

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