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
- Dozens more answers to tough SQL Server questions from Michael Hotek are available here.
- The Best Microsoft SQL Server Web Links: tips, tutorials, scripts, and more.
- The Best SQL Web Links
- Have a SQL Server tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL Server questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, SQL Server, DB2, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
This was first published in September 2003