TSQLScripts.com: Script Details

TSQLScripts.com.

skip to the main content area of this page

Transact SQL scripts written by
DBAs and Developers for
DBAs and Developers.

To submit a comment about this script click here here or to submit your own script click here.

Generate Database Role Permissions
Description:
This script will generate a list of all users and their corresponding database role(s). As an added bonus it provides a T-SQL Script that can be executed on the database. This can be useful when you want to replicate permissions from one server to another for a given database.
Submitted By: 2008DBA

select 
	roles.name Role_Name,
	members.name Member_Name,
	'exec sp_addrolemember ['+
	roles.name+
	'], ['+
	members.name+
	']' Grant_Permission
from sys.database_principals members
inner join sys.database_role_members drm
	on members.principal_id = drm.member_principal_id
inner join sys.database_principals roles
	on drm.role_principal_id = roles.principal_id
where members.name <> 'dbo'