Pensando na recuperação de um ambiente após uma catástrofe, resolvi desenvolver este script para backup dos logins com inclusão das contas que possuem permissão de sysadmin, sempre lembramos de realizar backup dos nossos databases, mas pouco se fala no backup dos logins.
O script abaixo irá gerar um arquivo com o nome do servidor, data, hora da geração e extensão .sql, abaixo, segue exemplo:
if object_id('tempdb..##tb_bkp_logins') is not null
drop table ##tb_bkp_logins
go
create table ##tb_bkp_logins (users varchar(8000));
declare @sql varchar(8000);
declare @server varchar(100);
declare @path varchar(500);
-- INSERT GENERATE LOGINS AND HASH PASSWORD
insert into ##tb_bkp_logins
select '--####################### START GENERATE LOGINS #######################'
union all
select ''
union all
select 'if(suser_id('+quotename(sp.name,'''')+') is null)begin create login '+quotename(sp.name)+
case when sp.type_desc = 'sql_login'
then ' with password = '+convert(nvarchar(max),sl.password_hash,1)+' hashed'
else ' from windows'
end + ';/*'+sp.type_desc+'*/ end;' collate sql_latin1_general_cp1_ci_as
from sys.server_principals as sp
left join sys.sql_logins as sl
on sp.principal_id = sl.principal_id
where sp.type_desc in ('sql_login','windows_group','windows_login')
and sp.name not like '##%##'
and sp.name not like '%nt service\%'
and sp.name not like '%win-%'
and sp.name not like '%nt authority\%'
and sp.name not in ('sa','nt authority\system');
-- INSERT GENERATE LOGINS SYSADMIN
insert into ##tb_bkp_logins
select '--####################### END GENERATE LOGINS #######################'
union all
select ''
union all
select '--####################### START ADD LOGINS TO SYSADMIN #######################'
union all
select 'exec master..sp_addsrvrolemember @loginame = n'+''''+[loginname]+''''+', @rolename = n'+''''+'sysadmin'+''''
from sys.server_principals p
join sys.syslogins s
on p.sid = s.sid
where p.type_desc in ('sql_login', 'windows_login', 'windows_group')
and p.name not like '##%'
and p.name not like '%nt service\%'
and p.name not like '%nt authority\%'
and p.name not in ('sa')
-- logins that are sysadmins
and s.sysadmin = 1
union all
select '--####################### END ADD LOGINS TO SYSADMIN #######################'
union all
select ''
union all
select '--####################### BEGIN GET DISABLE LOGINS #######################'
union all
SELECT 'ALTER LOGIN '+'['+name+']'+' DISABLE;'
FROM sys.server_principals sp
where is_disabled =1
AND SP.name NOT LIKE '##%##'
AND SP.name NOT LIKE '%NT SERVICE\%'
AND SP.name NOT LIKE '%WIN-%'
AND SP.name NOT LIKE '%NT AUTHORITY\%'
AND SP.type_desc <> 'SERVER_ROLE'
AND SP.name NOT IN ('SA','NT AUTHORITY\SYSTEM')
union all
select '--####################### END GET DISABLE LOGINS #######################'
union all
select ''
union all
select '--####################### BEGIN GET ENABLE LOGINS #######################'
union all
SELECT 'ALTER LOGIN '+'['+name+']'+' ENABLE;'
FROM sys.server_principals sp
where is_disabled =0
AND SP.name NOT LIKE '##%##'
AND SP.name NOT LIKE '%NT SERVICE\%'
AND SP.name NOT LIKE '%WIN-%'
AND SP.name NOT LIKE '%NT AUTHORITY\%'
AND SP.type_desc <> 'SERVER_ROLE'
AND SP.name NOT IN ('SA','NT AUTHORITY\SYSTEM')
-- GET INSTANCE NAME OR SERVER NAME
set @server=isnull(convert(varchar(100),SERVERPROPERTY ('InstanceName')),@@SERVERNAME);
-- SET THE PATCH FOR BACKUP LOCATION LOGINS
set @path='F:\BACKUP_DATABASES\SCRIPTS\LOGINS\'
select @sql = 'bcp "select * from ##tb_bkp_logins" queryout '+'"'+@path+@server+'_bkp_logins_'+REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR,GETDATE(),120),' ','_'),':','_'),'-','')+'.sql" -c -t; -T -S '+ @@servername
exec xp_cmdshell @sql;
drop table ##tb_bkp_logins;
Arquivos gerados:
Conteúdo do arquivo gerado:
--####################### START GENERATE LOGINS #######################
if(suser_id('usr_integracao') is null)begin create login [usr_integracao] with password = 0x020086B24D84B85145EC0D36D4B99A31980E7A38E90552B6F8CE4BD265AB6030BDEF9665D54ACC7A04FAE062 hashed;/*SQL_LOGIN*/ end;
if(suser_id('backup') is null)begin create login [backup] with password = 0x0200C05D789CC338F5563EF08E376BD40F40EC7CC78A864E5AE30351DF71D63228304E22F5059BEC38887A0CF hashed;/*SQL_LOGIN*/ end;
--####################### END GENERATE LOGINS #######################
--####################### START ADD LOGINS TO SYSADMIN #######################
exec master..sp_addsrvrolemember @loginame = n'usr_integracao', @rolename = n'sysadmin'
exec master..sp_addsrvrolemember @loginame = n'backup', @rolename = n'sysadmin'
--####################### END ADD LOGINS TO SYSADMIN #######################Com esta simples rotina, garantimos o histórico e backup dos nossos logins.
