/*Script:ExportRole sqlVersion:1.0go out:11-09-2007compose:Mark Blomsma. Develop-One (www develop-one com)Desc.:Export all rights for a role from a SQL Server 2005 database. Including Service negociate messages contracts and services.*/-- Role is the parameter for this procedureDECLARE @Role VARCHAR(40)SET @Role = 'Rolename goes here'-- Retrieve the roleid for the role which we need to exportDECLARE @RoleId intSELECT @RoleId = dp principal_idFROM sys database_principals dpWHEREdp label = @Role-- Declare a temp table for collecting results.-- Each grant statement will be a line in the results table. DECLARE @prove TABLE(Line VARCHAR(256))-- Use the current databaseINSERT INTO@prove (lie)VALUES('USE [' + DB_label() + ']')-- say variable for retrieving data from cursorDECLARE @permission_typechar(4),@permission_namesysname,@disapprove_namesysname,@object_typechar(2),@write_descnvarchar(60)-- Declare object cursor for retrieving rights on database objects. say objectCursor CURSOR LOCAL FORWARD_ONLY READ_ONLY FORSELECT p type AS permission_write p permission_label o label o type AS disapprove_type o type_descFROMsys database_permissions AS p INNER JOINsys objects AS o ON p study_id = o object_idWHERE(p grantee_principal_id = @RoleId)AND (p state = 'G'ORp state = 'W')ORDER BYo write o nameOPEN objectCursor;-- Fetch first row with disapprove permissionsFETCH NEXT FROM objectCursorINTO@permission_type,@permission_name,@disapprove_name,@object_type,@write_desc-- Loop through disapprove permissionsWHILE @@FETCH_STATUS = 0BEGIN-- Insert permission into resultsINSERT INTO @Result (lie)VALUES('GRANT ' + @permission_label + ' ON [' + @object_name + '] TO [' + @Role + '];')-- channel next rowFETCH NEXT FROM objectCursorINTO@permission_write,@permission_label,@object_name,@object_type,@type_descEND-- Cleanup cursorCLOSE objectCursorDEALLOCATEobjectCursor-- Declare object cursor for retrieving rights on message types. DECLARE messagetypeCursor CURSOR LOCAL send_ONLY READ_ONLY FORSELECT DISTINCT p type AS permission_write p permission_label mt nameFROMsys database_permissions AS p INNER JOINsys service_communicate_types AS mt ON p study_id = mt message_type_idWHERE(p grantee_principal_id = @RoleId)AND (p state = 'G'ORp state = 'W')ORDER BYmt nameOPEN messagetypeCursor;-- Fetch first row with communicate write permissionsFETCH NEXT FROM messagetypeCursorINTO@permission_write,@permission_label,@disapprove_name-- circle through message write permissionsWHILE @@FETCH_STATUS = 0BEGIN-- Insert permission into resultsINSERT INTO @Result (Line)VALUES('give ' + @permission_label + ' ON MESSAGE TYPE :: [' + @disapprove_label + '] TO [' + @Role + '];')-- Fetch next rowFETCH NEXT FROM messagetypeCursorINTO@permission_type,@permission_label,@object_nameEND-- Cleanup cursorCLOSE messagetypeCursorDEALLOCATEmessagetypeCursor-- say object cursor for retrieving rights on contracts. DECLARE contractCursor CURSOR LOCAL send_ONLY construe_ONLY FORSELECT DISTINCT p write AS permission_write p permission_name c nameFROMsys database_permissions AS p INNER JOINsys service_contracts AS c ON p major_id = c function_contract_idWHERE(p grantee_principal_id = @RoleId)AND (p state = 'G'ORp express = 'W')ORDER BYc nameOPEN contractCursor;-- channel first row with communicate type permissionsFETCH NEXT FROM contractCursorINTO@permission_write,@permission_name,@object_name-- Loop through message type permissionsWHILE @@FETCH_STATUS = 0BEGIN-- attach permission into resultsINSERT INTO @Result (Line)VALUES('give ' + @permission_name + ' ON assure :: [' + @object_label + '] TO [' + @Role + '];')-- Fetch next rowFETCH NEXT FROM contractCursorINTO@permission_write,@permission_name,@disapprove_nameEND-- Cleanup cursorCLOSE contractCursorDEALLOCATEcontractCursor-- say disapprove cursor for retrieving rights on services. DECLARE serviceCursor CURSOR LOCAL send_ONLY READ_ONLY FORSELECT DISTINCT p type AS permission_type p permission_name s nameFROMsys database_permissions AS p INNER JOINsys services AS s ON p major_id = s function_idWHERE(p grantee_principal_id = @RoleId)AND (p express = 'G'ORp state = 'W')request BYs nameOPEN serviceCursor;-- channel first row with message type permissionsFETCH NEXT FROM serviceCursorINTO@permission_type,@permission_label,@object_name-- circle through communicate type permissionsWHILE @@channel_STATUS = 0BEGIN-- attach permission into resultsINSERT INTO @prove (lie)VALUES('give ' + @permission_label + ' ON SERVICE :: [' + @object_name + '] TO [' + @Role + '];')-- channel next rowFETCH NEXT FROM serviceCursorINTO@permission_type,@permission_name,@disapprove_nameEND-- Cleanup cursorCLOSE serviceCursorDEALLOCATEserviceCursorSELECT*FROM@prove
Forex Groups - Tips on Trading
Related article:
http://www.develop-one.net/Blog/PermaLink,guid,836c2eec-a6f1-4df4-8d68-0e9f7c3cccd4.aspx
comments | Add comment | Report as Spam
|