I needed to export all rights granted to a specific role from a SQL Server 2005 database in order to check these roles into our TFS server.

/*
	Script	:	ExportRole.sql
	Version	:	1.0
	Date	:	11-09-2007
	Author	:	Mark Blomsma, Develop-One (www.develop-one.com)
	Desc.	:	Export all rights for a role from a SQL Server 2005 database.
			Including Service Broker messages, contracts and services.
*/

-- Role is the parameter for this procedure
DECLARE @Role VARCHAR(40)
SET @Role = 'Rolename goes here'

-- Retrieve the roleid for the role which we need to export
DECLARE @RoleId int
SELECT 
	@RoleId = dp.principal_id
FROM
	 sys.database_principals dp
WHERE
	dp.name = @Role

-- Declare a temp table for collecting results.
-- Each grant statement will be a line in the results table.
DECLARE @Result 
TABLE
	(
		Line VARCHAR(256)
	)

-- Use the current database
INSERT INTO
	@Result (Line)
VALUES
	('USE [' + DB_NAME() + ']')

-- Declare variable for retrieving data from cursor
DECLARE 
	@permission_type	char(4),
	@permission_name	sysname,
	@object_name		sysname,
	@object_type		char(2),
	@type_desc			nvarchar(60)

-- Declare object cursor for retrieving rights on database objects.
DECLARE objectCursor CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR
SELECT     
	p.type AS permission_type, 
	p.permission_name, 
	o.name, 
	o.type AS object_type, 
	o.type_desc
FROM
	sys.database_permissions AS p 
INNER JOIN
	sys.objects AS o ON p.major_id = o.object_id
WHERE
	(p.grantee_principal_id = @RoleId)
AND (
		p.state = 'G'
		OR
		p.state = 'W'
	)
ORDER BY
	o.type, o.name

OPEN 
	objectCursor;

-- Fetch first row with object permissions
FETCH NEXT FROM 
	objectCursor
INTO
	@permission_type,
	@permission_name,
	@object_name,
	@object_type,
	@type_desc	

-- Loop through object permissions
WHILE @@FETCH_STATUS = 0
BEGIN
	-- Insert permission into results
	INSERT INTO 
		@Result (Line)
	VALUES
		('GRANT ' + @permission_name + ' ON [' + @object_name + '] TO [' + @Role + '];')

	-- Fetch next row
	FETCH NEXT FROM 
		objectCursor
	INTO
		@permission_type,
		@permission_name,
		@object_name,
		@object_type,
		@type_desc	
END

-- Cleanup cursor
CLOSE 
	objectCursor
DEALLOCATE
	objectCursor

-- Declare object cursor for retrieving rights on message types.
DECLARE messagetypeCursor CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR
SELECT DISTINCT    
	p.type AS permission_type, 
	p.permission_name, 
	mt.name
FROM
	sys.database_permissions AS p 
INNER JOIN
	sys.service_message_types AS mt ON p.major_id = mt.message_type_id
WHERE
	(p.grantee_principal_id = @RoleId)
AND (
		p.state = 'G'
		OR
		p.state = 'W'
	)
ORDER BY
	mt.name

OPEN 
	messagetypeCursor;

-- Fetch first row with message type permissions
FETCH NEXT FROM 
	messagetypeCursor
INTO
	@permission_type,
	@permission_name,
	@object_name	

-- Loop through message type permissions
WHILE @@FETCH_STATUS = 0
BEGIN
	-- Insert permission into results
	INSERT INTO 
		@Result (Line)
	VALUES
		('GRANT ' + @permission_name + ' ON MESSAGE TYPE :: [' + @object_name + '] TO [' + @Role + '];')

	-- Fetch next row
	FETCH NEXT FROM 
		messagetypeCursor
	INTO
		@permission_type,
		@permission_name,
		@object_name
END

-- Cleanup cursor
CLOSE 
	messagetypeCursor
DEALLOCATE
	messagetypeCursor


-- Declare object cursor for retrieving rights on contracts.
DECLARE contractCursor CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR
SELECT DISTINCT    
	p.type AS permission_type, 
	p.permission_name, 
	c.name
FROM
	sys.database_permissions AS p 
INNER JOIN
	sys.service_contracts AS c ON p.major_id = c.service_contract_id
WHERE
	(p.grantee_principal_id = @RoleId)
AND (
		p.state = 'G'
		OR
		p.state = 'W'
	)
ORDER BY
	c.name

OPEN 
	contractCursor;

-- Fetch first row with message type permissions
FETCH NEXT FROM 
	contractCursor
INTO
	@permission_type,
	@permission_name,
	@object_name	

-- Loop through message type permissions
WHILE @@FETCH_STATUS = 0
BEGIN
	-- Insert permission into results
	INSERT INTO 
		@Result (Line)
	VALUES
		('GRANT ' + @permission_name + ' ON CONTRACT :: [' + @object_name + '] TO [' + @Role + '];')

	-- Fetch next row
	FETCH NEXT FROM 
		contractCursor
	INTO
		@permission_type,
		@permission_name,
		@object_name
END

-- Cleanup cursor
CLOSE 
	contractCursor
DEALLOCATE
	contractCursor



-- Declare object cursor for retrieving rights on services.
DECLARE serviceCursor CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR
SELECT DISTINCT    
	p.type AS permission_type, 
	p.permission_name, 
	s.name
FROM
	sys.database_permissions AS p 
INNER JOIN
	sys.services AS s ON p.major_id = s.service_id
WHERE
	(p.grantee_principal_id = @RoleId)
AND (
		p.state = 'G'
		OR
		p.state = 'W'
	)
ORDER BY
	s.name

OPEN 
	serviceCursor;

-- Fetch first row with message type permissions
FETCH NEXT FROM 
	serviceCursor
INTO
	@permission_type,
	@permission_name,
	@object_name	

-- Loop through message type permissions
WHILE @@FETCH_STATUS = 0
BEGIN
	-- Insert permission into results
	INSERT INTO 
		@Result (Line)
	VALUES
		('GRANT ' + @permission_name + ' ON SERVICE :: [' + @object_name + '] TO [' + @Role + '];')

	-- Fetch next row
	FETCH NEXT FROM 
		serviceCursor
	INTO
		@permission_type,
		@permission_name,
		@object_name
END

-- Cleanup cursor
CLOSE 
	serviceCursor
DEALLOCATE
	serviceCursor



SELECT
	*
FROM
	@Result