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