Granting permissions to users (or roles) in SQL Server can be a pain, especially when there is a large number of tables and user/roles involved. Faced with this task, I went looking for a script to do so, and found it at SQL Server 2005: Easily Grant Select to All Tables and/or Views in a Database. The script is pretty good, but I made a few modifications to replace the cursor, use SQL2K5 system views and limit by schema. This is an easy way to grant permissions for a database role to a specific schema.
Two system views are mentioned–sys.database_principals, which provides a list of users and roles in a database, and sys.schemas, which lists the schemas and the schema IDs for the database. I'm not a big fan of cursors, I prefer to use a while loop. I feel while loops are easier to write and perform better. On a small task like this, the performance difference is negligable, but the ease of coding is nice.
Here is the query I ended up with:
@login can be a specific user, or a role; can be obtained by querying sys.database_principals
@schema_id = query sys.schemas for list of schema ids
declare @login varchar(50), @schema_id int
set @login = 'username'
set @schema_id = 1
declare @objects table (objid int identity(1,1), cmd varchar(500))
insert into @objects
select 'grant select on ' + name + ' to ' + @login
where schema_id = @schema_id
select * From @objects
declare @oldid int, @curid int, @maxid int, @cmd varchar(500)
set @oldid = 0
select @maxid = count(objid) from @objects
while @oldid < @maxid
select top 1 @curid = objid,
@cmd = cmd
order by objid
set @oldid = @curid