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
Leave A Comment?