How can I implement "Row Level Security" in SQL Server 2000?

Thanks alot.

Generally speaking, you can create views to horizontally partition data
based on your security requirements. The views filter data so that only
authorized users can access data. Do not allow direct access to the
underlying tables and grant user permissions only on views.

The script below illustrates this technique to implement security so that
users can only access employee data for those departments they are allowed
to see, based on their login and entries in the SecurityByDepartment table.
This approach can be extended to include application-defined roles in order
to reduce security administration.

SET NOCOUNT ON
GO

CREATE TABLE Employees
(
EmployeeId int NOT NULL
CONSTRAINT PK_Employee
PRIMARY KEY NONCLUSTERED,
DepartmentId int NOT NULL,
SomeData varchar(30)
)
CREATE CLUSTERED INDEX Employees_cdx ON Employees(DepartmentId)
GO

CREATE TABLE SecurityByDepartment
(
UserName sysname NOT NULL,
DepartmentId int NOT NULL,
CONSTRAINT PK_SecurityByDepartment
PRIMARY KEY (UserName, DepartmentId)
)
GO

CREATE VIEW MyEmployees
AS
SELECT
e.EmployeeId,
e.DepartmentId,
e.SomeData
FROM Employees e
JOIN SecurityByDepartment sbd ON
sbd.UserName = SUSER_SNAME() AND
sbd.DepartmentId = e.DepartmentId
GO

EXEC sp_addrole 'MyRole'
GRANT SELECT ON MyEmployees TO MyRole
GO

INSERT INTO Employees VALUES(1, 1, 'some data 1')
INSERT INTO Employees VALUES(2, 1, 'some data 2')
INSERT INTO Employees VALUES(3, 1, 'some data 3')
INSERT INTO Employees VALUES(4, 2, 'some data 4')
INSERT INTO Employees VALUES(5, 2, 'some data 5')
GO

INSERT INTO SecurityByDepartment VALUES('Login1' ,1)
INSERT INTO SecurityByDepartment VALUES('Login1' ,2)
INSERT INTO SecurityByDepartment VALUES('Login2' ,1)
INSERT INTO SecurityByDepartment VALUES('Login3' ,2)
GO

EXEC sp_addlogin 'Login1'
EXEC sp_grantdbaccess 'Login1'
EXEC sp_addrolemember 'MyRole', 'Login1'
EXEC sp_addlogin 'Login2'
EXEC sp_grantdbaccess 'Login2'
EXEC sp_addrolemember 'MyRole', 'Login2'
EXEC sp_addlogin 'Login3'
EXEC sp_grantdbaccess 'Login3'
EXEC sp_addrolemember 'MyRole', 'Login3'
GO

PRINT SUSER_SNAME()
SELECT * FROM MyEmployees
GO

SETUSER 'Login1'
PRINT SUSER_SNAME()
SELECT * FROM MyEmployees
GO

SETUSER
SETUSER 'Login2'
PRINT SUSER_SNAME()
SELECT * FROM MyEmployees
GO

SETUSER
SETUSER 'Login3'
PRINT SUSER_SNAME()
SELECT * FROM MyEmployees
GO

SETUSER
GO

DROP VIEW MyEmployees
DROP TABLE Employees
DROP TABLE SecurityByDepartment
EXEC sp_revokedbaccess 'Login1'
EXEC sp_droplogin 'Login1'
EXEC sp_revokedbaccess 'Login2'
EXEC sp_droplogin 'Login2'
EXEC sp_revokedbaccess 'Login3'
EXEC sp_droplogin 'Login3'
EXEC sp_droprole 'MyRole'
GO


--
Hope this helps.

Dan Guzman
SQL Server MVP

"Elham.Ghoddousi" <elham_gh@yahoo.com> wrote in message
news:4c3908a6.0409040503.43a24c3d@posting.google.c om...
> How can I implement "Row Level Security" in SQL Server 2000?
>
> Thanks alot.