Solved

Location of permissions info in system tables

Posted on 2008-10-08
1
299 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
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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

708 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now