Is there a shortcut to settings a set of bits in an MS SQL 2005 table.

So you have a series of columns that can be added by the user?  It really would be best served to break them out into a name/value pair table so that the user just adds records to the database when a new security permission is added, and not a new column.

Ah. A permissions table. Good point. A little more work, but a much better way.

If I'd started the project (rather than just picking it up), I think I may have eventually come round to that way of thinking.

But there is something to be said for having a 1 row = 1 account. Purity vs practicality. Though views and SPs are probably the better way to go anyway - hide the storage from the programming interface.

The permissions are the only thing the end users can add and they are only ever going to be bit columns. Using the column description to hold the meaning/label of the permission is really useful. And one of the rules of normalisation is that 1 column should never hold different data - ok, an interpretation here is that permission A is different data to permission B. Also as a user will not have 2 permission A's, a subtable is not best served - at least as I understand normalisation - but again purity vs practicality comes into play here.

Overall, I think I like the structure as it is (mainly the app is not really ready for any significant amount of reworking).

"Also as a user will not have 2 permission A's"

That can be managed with a unique constraint on the Permission_id and user_id.

What format is '{$a_ValidPerms.join(',')}' in?   If it's a comma delimited list, you can use a function I have written and used to split it out into records.  From there, you can generate dynamic SQL.



Using this function:

if object_id('[dbo].[fn_DelimitedToTable]') is not null
     drop function [dbo].[fn_DelimitedToTable]
go
create function [dbo].[fn_DelimitedToTable](@DelimitedString nvarchar(max), @Delimiter nvarchar(32))
returns @Values TABLE
     (ident         int not null identity primary key clustered
     ,thePosition   int not null
     ,theValue      nvarchar(max)
     )
as
/************************************************************
*
*    Author:        Brandon Galderisi
*    Last modified: 07-Oct-2008
*    Purpose:       splits an input string (@DelimitedString)
*                   on a delimiter (@delimiter) and outputs
*                   a table of values.
*    
*
*************************************************************/
begin

insert into @Values (thePosition,theValue)
            select n, substring(@delimiter + @DelimitedString + @delimiter, n + (datalength(@delimiter)/2), charindex(@delimiter, @delimiter + @DelimitedString + @delimiter, n + datalength(@delimiter)/2) - n - datalength(@delimiter)/2) as string_value
            from      dbo.vw_Nums
            where
                  n <= (datalength(@delimiter + @DelimitedString + @delimiter)/2) - (datalength(@delimiter)/2)
                  and substring(@delimiter + @DelimitedString + @delimiter, n, (datalength(@delimiter)/2)) = @delimiter



return
end
/*
-- The purpose of vw_Nums is if the source database does not have a numbers table.  This view (vw_nums)
-- will allow the parsing of up to 4 billion+ character strings with the above function.  Whether a static
-- table or this view is used for fn_DelimitedToTable, it can only split a string as long as the source
-- numbers table.
Requires a nunbers table or this view:
create view vw_Nums
as
with   cte0 as (select 1 as c union all select 1),
       cte1 as (select 1 as c from cte0 a, cte0 b),
       cte2 as (select 1 as c from cte1 a, cte1 b),
       cte3 as (select 1 as c from cte2 a, cte2 b),
       cte4 as (select 1 as c from cte3 a, cte3 b),
       cte5 as (select 1 as c from cte4 a, cte4 b),
       nums as (select row_number() over (order by c) as n from cte5)
       select n from nums



Sample Usage:
select * from [dbo].[fn_DelimitedToTable]('a|%25basdf|%25c|%25d','|%25')
select theValue from [dbo].[fn_DelimitedToTable]('a','|')
select * from [dbo].[fn_DelimitedToTable]('a basdf c d',' ')
*/
GO

create usp_SetPermissions
 @i_User  int
,@s_Perms nvarchar(max)
as
declare @SQL nvarchar(max)
 
select @SQL = coalesce(@SQL + N'
', N'') + N'update Users SET Permission' + theValue + N' = 1 where userid = @i_user'
from [dbo].[fn_DelimitedToTable](@s_Perms,',')
 
exec sp_executesql @SQL, '@i_user int', @i_user = @i_user

Select allOpen in new window

Thanks for that. Fundamentally, it is a case of constructing the individual SETs. That's what I expected, but hoped for something different.

Can definitely work with that!