Solved

Location of permissions info in system tables

Posted on 2008-10-08
1
306 Views
Last Modified: 2012-05-05
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?

0
Comment
Question by:looper8
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 1

Accepted Solution

by:
looper8 earned 0 total points
ID: 22670106
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

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question