October 6, 2011 at 2:50 pm
Hi, back again, sorry its been so long, been on holiday and generally very busy.
Remi, this code will reverse engineer application roles
select 'CREATE APPLICATION ROLE ['+ name + '] with password = '+QUOTENAME('insertpwdhere','''')+' ,default_schema = ['+default_schema_name+']'
from sys.database_principals
where type = 'A'
I have decided I have to put a placeholder for the password, because
a) to recreate the role the password needs to be in plain text and to print them out would be a security risk
b) I cannot see a way to reverse engineer the password into clear text
so I am afraid you would need to know the passwords and manually enter them. Having 98 application roles seems a lot!
edit: I will post the complete script tomorrow
---------------------------------------------------------------------
October 7, 2011 at 3:36 am
ok, the final code taken as far as I need to. Use this to reverse engineer database permissions (SQL2005 and up) if you want to reapply them after a restore from another server, or just for documentation\DR purposes.
/****** Object: StoredProcedure [dbo].[usp_get_object_permissions] Script Date: 10/07/2011 14:28:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- run this script from the user database
--exec usp_get_object_permissions
--drop proc usp_get_object_permissions
--go
ALTER PROCEDURE [dbo].[usp_get_object_permissions]
AS
set nocount on
set quoted_identifier off
declare @as_ObjectName sysname
set @as_ObjectName = NULL
--database owner info
select 'alter authorization on database::['+db_name()+'] to ['+ suser_sname(owner_sid)+']'
AS '--owner of database when script was created'
from master.sys.databases where name = db_name()
--drop and recreate users
select '-- It is not always necessary to drop and recreate the users it will depend on the circumstances under which you need to run this script'
select 'drop user [' + name + ']' from sys.database_principals
where principal_id > 4 and owning_principal_id is NULL
and type != 'A'
order by name
select 'CREATE USER [' + dp.name collate database_default + '] FOR LOGIN [' + sp.name + ']'+
case dp.type
when 'G' then ' '
else
' WITH DEFAULT_SCHEMA=['+dp.default_schema_name + ']'
end
as '-- by default Orphaned users will not be recreated'
from sys.server_principals sp
inner join sys.database_principals dp on dp.sid = sp.sid
where dp.principal_id > 4 and dp.owning_principal_id is NULL and sp.name <> ''
order by dp.name
-- Recreate the User defined roles
select '-- server created roles should be added by the correct processes'
select 'CREATE ROLE ['+ name + '] AUTHORIZATION ['+USER_NAME(owning_principal_id)+']'
from sys.database_principals
where name != 'public' and type = 'R' and is_fixed_role = 0
-- recreate application roles
select 'CREATE APPLICATION ROLE ['+ name + '] with password = '+QUOTENAME('insertpwdhere','''')+' ,default_schema = ['+default_schema_name+']'
from sys.database_principals
where type = 'A'
-- ADD ROLE MEMBERS
SELECT 'EXEC sp_addrolemember [' + dp.name + '], [' + USER_NAME(drm.member_principal_id) + '] ' AS [-- AddRolemembers]
FROM sys.database_role_members drm
INNER JOIN sys.database_principals dp ON dp.principal_id = drm.role_principal_id
where USER_NAME(drm.member_principal_id) != 'dbo'
order by drm.role_principal_id
-- CREATE GRANT Object PERMISSIONS SCRIPT
SELECT replace(state_desc,'_with_grant_option','') + ' '+ permission_name + ' ON ['
+ OBJECT_SCHEMA_NAME(major_id) + '].[' + OBJECT_NAME(major_id) + ']'+
case minor_id
when 0 then ' '
else
' (['+col_name(sys.database_permissions.major_Id, sys.database_permissions.minor_id) + '])'
end
+' TO [' + USER_NAME(grantee_principal_id)+']' +
case
when state_desc like '%with_grant_option' then ' with grant option'
else
' '
end
as '-- object/column permissions'
FROM sys.database_permissions (NOLOCK)
WHERE class not in (0,3) and major_id = ISNULL(OBJECT_ID(@as_ObjectName), major_id)
--AND OBJECT_SCHEMA_NAME(major_id) != 'SYS'
ORDER BY USER_NAME(grantee_principal_id),OBJECT_SCHEMA_NAME(major_id), OBJECT_NAME(major_id)
--SCHEMA permissions
SELECT replace(state_desc,'_with_grant_option','') + ' '+ permission_name + ' ON SCHEMA::['
+ SCHEMA_NAME(major_id) + ']'+
+' TO [' + USER_NAME(grantee_principal_id)+']' +
case
when state_desc like '%with_grant_option' then ' with grant option'
else
' '
end
as '-- Schema permissions'
FROM sys.database_permissions (NOLOCK)
WHERE class_desc = 'SCHEMA'
ORDER BY USER_NAME(grantee_principal_id),SCHEMA_NAME(major_id)
SELECT replace(state_desc,'_with_grant_option','') + ' '+ permission_name +
' TO [' + USER_NAME(grantee_principal_id)+']' +
case
when state_desc like '%with_grant_option' then ' with grant option'
else
' '
end
FROM sys.database_permissions (NOLOCK)
WHERE permission_name = 'VIEW DEFINITION' and class_desc = 'database'
ORDER BY USER_NAME(grantee_principal_id)
---------------------------------------------------------------------
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy