Simple Security String

/*
SecurityString.sql
author: Lars Rahm
date: 7/19/2002

description: A procedure to generate a simple security string of 0's and 1's by userId.

*/

set nocount on

declare @SecurityString varchar(15)

/* Initiate the string to all 0's. *//***********************************/set @SecurityString = REPLICATE(0,15)

create table #SecurityTable (
userId varchar(20),
securityPosition int ,
securityCode varchar(20) )


insert into #SecurityTable values ( 'Nils' , 1 , 'CREATE_ORDER')
insert into #SecurityTable values ( 'Nils' , 3 , 'VIEW_REPORTS')
insert into #SecurityTable values ( 'Nils' , 7 , 'EXECUTE')
insert into #SecurityTable values ( 'Nils' , 14 , 'EDIT_USERS')
insert into #SecurityTable values ( 'Trina' , 5 , 'DELETE')
insert into #SecurityTable values ( 'Paula' , 3 , 'READ' )
insert into #SecurityTable values ( 'Raj' , 13 , 'WRITE' )

/* Loop through the #SecurityTable and update the @SecurityString setting O's to 1's by securtyPosition. *//*********************************************************************************************************/select  @SecurityString =  isnull(stuff(@SecurityString , securityPosition,1,'1'),@SecurityString) 
from #SecurityTable
where userId = 'Nils'

select 'Nils' as UserId , @SecurityString as SecurityString

drop table #SecurityTable