Перенос логинов SQLServer с одного сервера на другой

Перенос инстансов  SQLServer с одного сервера на другой – иногда это ежедневная работа админов DBA  И многое  в этом процессе хочется упростить и автоматизировать, например перенос логинов.

Если база переносится путем  Backup\Restore, то пользователи переносятся вместе с БД, а вот логины аутентификации на сервер остаются не захваченными .  Логины можно легко переносить с помощью SSIS задачей Transfer Logins Task.  Например, я создала пакет For Admins Transfer Logins (на рис. ниже), содержащий задачу Transfer Logins Task, далее настроила источник Source, т.е сервер с которого копируем логины , и приемник Destination, сервер, на который копируем логины. Выбрала списком необходимые логины-> запуск-> и пожалуйста, логины успешно перехали на новый сервер :)

Для переноса SID-ов надо выбрать CopySids=True.
Также логины можно переносить готовыми скриптами, как описано по ссылке
http://support.microsoft.com/kb/246133

После переноса логинов можно делать проверки следующими скриптами:

--1) Расхождения на уровне server permissions

select * from

(

select u.name,t.[type],permission_name from old_server.gradient_dw.sys.server_permissions t

left join

old_server.gradient_dw.sys.server_principals u

on t.grantee_principal_id=principal_id

except

select u.name,t.[type],permission_name from new_server.gradient_dw.sys.server_permissions t

left join

new_server.gradient_dw.sys.server_principals u

on t.grantee_principal_id=principal_id

)t

--2) Расхождения на уровне  пользователей БД

select distinct status,name,altuid,islogin,isntuser,isntgroup from old_server.gradient_dw.sys.sysusers

except

select distinct status,name,altuid,islogin,isntuser,isntgroup from new_server.gradient_dw.sys.sysusers

--3) Расхождения на уровне прав на объекты БД

SELECT o.type_desc,o.nmobject, p.name AS NmUser, b.class_desc,b.permission_name,b.state_desc

FROM old_server.gradient_dw.sys.database_permissions b  JOIN

old_server.gradient_dw.sys.database_principals p ON p.principal_id = b.grantee_principal_id  AND p.name NOT IN ('guest','public')

JOIN (

SELECT o.object_id, o.type_desc,s.name NmSchem,o.name NmObject FROM old_server.gradient_dw.sys.objects O LEFT JOIN old_server.gradient_dw.sys.schemas  S ON O.schema_id=S.schema_id

WHERE o.type_desc IN  ('INTERNAL_TABLE','SQL_INLINE_TABLE_VALUED_FUNCTION',

'SQL_SCALAR_FUNCTION','SQL_STORED_PROCEDURE','SQL_TABLE_VALUED_FUNCTION',

'SYSTEM_TABLE','USER_TABLE','VIEW','...') 

) O  ON o.object_id=b.major_id

except

SELECT o.type_desc,o.nmobject, p.name AS NmUser, b.class_desc,b.permission_name,b.state_desc

FROM new_server.gradient_dw.sys.database_permissions b  JOIN

new_server.gradient_dw.sys.database_principals p ON p.principal_id = b.grantee_principal_id  AND p.name NOT IN ('guest','public')

JOIN (

SELECT o.object_id, o.type_desc,s.name NmSchem,o.name NmObject FROM new_server.gradient_dw.sys.objects O LEFT JOIN new_server.gradient_dw.sys.schemas  S ON O.schema_id=S.schema_id

WHERE o.type_desc IN  ('INTERNAL_TABLE','SQL_INLINE_TABLE_VALUED_FUNCTION',

'SQL_SCALAR_FUNCTION',SQL_STORED_PROCEDURE','SQL_TABLE_VALUED_FUNCTION',

'SYSTEM_TABLE','USER_TABLE','VIEW','...')

) O  ON o.object_id=b.major_id

(2 и 3 есть смысл проверять, если база развернута с  другого города или с тест, например при формировании нового города)