Location of permissions info in system tables

I want to copy the permissions on a role to a new role in the same database.  How should I do that?

I wondered about creating a query to select the permissions for the old role from system tables, and then looping through that list and for each one doing GRANT x ON y TO z ... Mind you I don't know where to find the permissions in the system tables.  Is that a good idea or is there a better way?

LVL 1
looper8Asked:
Who is Participating?
 
looper8Connect With a Mentor Author Commented:
See the code snippet for SP which gets details of what permissions there are for a particular user or role
(taken from http://www.dbforums.com/showthread.php?t=843672)
In my situation can be run as follows: dbo.adm_script_permissions NULL, 'FMS_MPS'
CREATE PROC dbo.adm_script_permissions
(
@name ncharacter varying(776) = NULL,
@username sysname = NULL,
@grantorname sysname = NULL,
@permissionarea character varying(10) = 'o s'
)AS
BEGIN
 
SET NOCOUNT ON
 
CREATE Table 	#tmp_protect_data 
( 
		OwnerName sysname NOT Null
		, ObjectName sysname NOT Null
		, GranteeName sysname NOT Null
		, GrantorName sysname NOT Null
		, ProtectType char(10) Null
		, ActionName varchar(20) Null
		, ColumnName sysname Null
)
 
SET 		@username = PARSENAME ( @username , 1 ) -- Remove []
 
INSERT INTO 	#tmp_protect_data (OwnerName, ObjectName, GranteeName, GrantorName, ProtectType, ActionName, ColumnName )
 
EXEC 		sp_helprotect @name = @name, @username = @username, @grantorname = @grantorname, @permissionarea = @permissionarea
 
SELECT 		RTRIM(ProtectType) + ' ' + ActionName +
		CASE
			WHEN ColumnName IN ('.','(All+New)','(All)','(New)') THEN ''
			ELSE ' (' + ColumnName + ')'
		END +
		' ON ' + OwnerName + '.' + ObjectName + ' TO ' + GranteeName + char(13)+char(10)
 
FROM 		#tmp_protect_data
WHERE 		ColumnName IS NOT NULL  -- ColumnName is NULL on deleted columns
AND 		OwnerName <> 'INFORMATION_SCHEMA' 
AND 		ObjectName NOT LIKE 'dt[_]%' 
AND 		ObjectName NOT IN ('dtproperties')
 
ORDER BY 	OwnerName + '.' + ObjectName, GranteeName, ActionName, RTRIM(ProtectType)
 
DROP Table 	#tmp_protect_data
 
SET NOCOUNT OFF
 
END

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.