Disable sql logins

Configurare noua (How To)

Situatie

Avem nevoie uneori sa dam disable rapid la toti logins de sql , mai putin la cei care sunt sysadmin, pentru diferite operatiuni de mentenanta sau mai grav, pentru cazul in care ceva a fost alterat gresit in baza si e nevoie de un restore.

Pentru aceasta situatie am facut procedura de mai jos.

Solutie

create procedure [dbo].[BD_Disable_logins_fara_sysadmin_prc]

as

declare @login varchar(100)
declare @sql varchar(100)

declare logini cursor static forward_only
for
select sp.name
from sys.server_principals sp
left join sys.server_role_members srm on sp.principal_id=srm.member_principal_id
where
sp.type=’S’
and
isnull(srm.role_principal_id,99)<>3
and charindex(‘#’,sp.name ) = 0
and sp.name<>’sa’

order by 1

open logini
fetch next from logini into @login

while @@fetch_status = 0
begin
set @sql = ‘alter login [‘+@login+’] DISABLE’
exec (@sql)
fetch next from logini into @login
end

close logini
deallocate logini

Tip solutie

Permanent
Etichetare:

Voteaza

(4 din 5 persoane apreciaza acest articol)

Despre Autor

Leave A Comment?