Перенос логинов между Microsoft SQL серверами.

добавлено: 20 фев 16
понравилось:0
просмотров: 1388
комментов: 0

теги:

Автор: Алексей Куренков

Тема довольно таки старая и избитая. Необходимость периодически возникает при апгрейде или миграции сервера. Но вот просто в лоб на версии 2014 у меня уже эти процедуры не заработали - отсутствуют некоторые динамические представления, на которых построена эта процедура.
Посему пришлось писать свой перенос со старого сервера на новый, с паролями и SIDами.

Надеюсь еще кому пригодится.

+ На мой взгляд попроще запрос

-- здесь рекомендую нажать Ctrl+T в Management Studio перед выполнением ))
select
	'create login ['+name+'] with
	password = '+convert(varchar(max),password_hash,1)+' hashed,
	sid = '+convert(varchar(max),sid,1)+',
	default_database = ['+default_database_name+'],
	default_language = '+default_language_name+',
	check_expiration = '+iif(is_expiration_checked=0,'off','on')+',
	check_policy = '+iif(is_policy_checked=0,'off','on')+'
go
alter login ['+name+'] '+case when is_disabled=1
	then 'disable' else 'enable'
end+'
go' as SQL_COMMAND
from sys.sql_logins
where sid != 0x01
and name not like '##%'

union all

select 'create login ['+name+'] from windows with
	default_database = ['+default_database_name+'],
	default_language = '+default_language_name+'
go
alter login ['+name+'] '+case when is_disabled=1
	then 'disable' else 'enable'
end+'
go'
from sys.server_principals
where type = 'U'
and name not like 'NT AUTHORITY\%'
and name not like 'NT Service\%'

union all

select
	'alter server role '+txt+' add member ['+loginname+']
go'
from sys.syslogins l
cross apply
(values
	(denylogin,'denylogin'),
	(sysadmin,'sysadmin'),
	(securityadmin,'securityadmin'),
	(serveradmin,'serveradmin'),
	(setupadmin,'setupadmin'),
	(processadmin,'processadmin'),
	(diskadmin,'diskadmin'),
	(dbcreator,'dbcreator'),
	(bulkadmin,'bulkadmin')
) o (opt, txt)
where sid != 0x01
and name not like '##%'
and name not like 'NT AUTHORITY\%'
and name not like 'NT Service\%'
and opt != 0


+ А этот немного модифицированный, не во всех ManagementStudio перенос строки копипастится нормально

with cte (num, query, a,b,c,d,e,f,g,h,i,j) as
(
select
row_number() over (order by name), 1,
'create login ['+name+'] with',
'	password = '+convert(varchar(max),password_hash,1)+' hashed,',
'	sid = '+convert(varchar(max),sid,1)+',',
'	default_database = ['+default_database_name+'],',
'	default_language = '+default_language_name+',',
'	check_expiration = '+iif(is_expiration_checked=0,'off','on')+',',
'	check_policy = '+iif(is_policy_checked=0,'off','on'),
'go',
'alter login ['+name+'] '+case when is_disabled=1 
	then 'disable' else 'enable'
end,
'go'
from sys.sql_logins
where sid != 0x01
and name not like '##%'

union all

select
row_number() over (order by name), 2,
'create login ['+name+'] from windows with',
'	default_database = ['+default_database_name+'],',
'	default_language = '+default_language_name,
'alter login ['+name+'] '+case when is_disabled=1
	then 'disable' else 'enable'
end,'go',null,null,null,null,null
from sys.server_principals
where type = 'U'
and name not like 'NT AUTHORITY\%'
and name not like 'NT Service\%'

union all

select
row_number() over (order by name), 3,
	'alter server role '+txt+' add member ['+loginname+']'
	,'go',null,null,null,null,null,null,null,null
from sys.syslogins l
where sid != 0x01
and name not like 'NT AUTHORITY\%'
and name not like 'NT Service\%'
cross apply
(values
	(denylogin,'denylogin'),
	(sysadmin,'sysadmin'),
	(securityadmin,'securityadmin'),
	(serveradmin,'serveradmin'),
	(setupadmin,'setupadmin'),
	(processadmin,'processadmin'),
	(diskadmin,'diskadmin'),
	(dbcreator,'dbcreator'),
	(bulkadmin,'bulkadmin')
) o (opt, txt)
where sid != 0x01
and name not like '##%'
and name not like 'NT AUTHORITY\%'
and name not like 'NT Service\%'
and opt != 0
)
select
	o.command
from cte
cross apply
(values
	(num,'a',a),
	(num,'b',b),
	(num,'c',c),
	(num,'d',d),
	(num,'e',e),
	(num,'f',f),
	(num,'g',g),
	(num,'h',h),
	(num,'i',i),
	(num,'j',j)
) o (num,ord,command)
where o.command is not null
order by cte.query, o.num, o.ord

Ну и собственно, после выполнения запроса на исходном сервере копиапастим результат и в новом окне с подключением к новому серверу вставляем результат, для дальнейшего исполнения.

Материал взят с сайта автора.

Комментарии




Необходимо войти на сайт, чтобы оставлять комментарии