Granting Table and View Permissions in SQL Server 2005/2008

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
from sys.tables
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
begin
 select top 1 @curid = objid,
 @cmd = cmd
 from @objects
 where objid>@oldid
 order by objid
 
 execute (@cmd)
 select @cmd 
 set @oldid = @curid
end