dbawiki

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
  *  First copy the dump from \\\\spifls07.mon.local\\DATA.1013\\01_DBdumps\\SQL\\ to the target SQL Server in ONT
  *  Check the current security settings on the ONT database, use this script  [[Link List all permission in DB]].
  *  Restore over the existing database. Use the GUI interface to setup the restore, check the 'with replace' box, then script the statement to a new window.
  *  There alter the script to get the following result
<code>
  ALTER DATABASE [[DBToRestore]] SET Single_user with rollback immediate
  GO
  RESTORE DATABASE ...
</code>
  *  Let the restore do it's thing
  *  When finished, recreate the premission with the above generated script
  *  Eventually clean up the MON accounts ...
  *  If any SQL accounts are restored, fix them with the following statement
<code>
  exec sp_change_users_login 'auto_fix', 'usernametofix'
</code>


[[Category:SQL Server]]
[[Category:BackupRestore]]


<code>
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)
</code>
[[Category:SQL Server]]