Re: bitewise and

From: David Portas (REMOVE_BEFORE_REPLYING_dportas_at_acm.org)
Date: 12/05/04 

Date: Sun, 5 Dec 2004 16:24:36 -0000

Bitmap columns are a very poor way to store data in SQL. A basic principle
of good database design is that each column should represent a single
attribute. You haven't stated any reason to compromise that principle.

SQL Server supports user-defined roles for exactly the purpose you have
described. See Books Online for details. Alternatively you could create a
table something like the following to assign users to your own roles or
groups.

CREATE TABLE UserGroups (user_id INTEGER NOT NULL REFERENCES Users
(user_id), group_code CHAR(4) NOT NULL REFERENCES Groups (group_code),
PRIMARY KEY (user_id, group_code))

Then validate permissions like this:

EXISTS
 (SELECT *
   FROM UserGroups
   WHERE group_code = 'XXXX'
    AND user_id = @current_user) 

-- 
David Portas
SQL Server MVP
--