EL SIGUIENTE SCRIPT ELIMINA TODOS LOS USUARIOS DE TODAS LAS BASES EXISTENTE
DEL SERVIDOR, Y ELIMINA TODOS LOS LOGINS DEL SERVIDOR.
--
============================================================================================
-- AUTOR: OMAR OLGUÍN
-- FECHA: 2016 07 06
-- VERSIÓN: 1.0
-- REQUERIMIENTO: MTTO
-- DESCRIPCIÓN: ELIMINA TODOS LOS USUARIOS A NIVEL SERVIDOR Y BASE DE
DATOS
-- EXCEPTO
USUARIOS QUE ESTÁN INTENCIONALMENTE
-- EN
CÓDIGO DURO DENTRO DE ÉSTE SCRIPT PARA NO BORRARLOS
-- COMO: 'SA', 'DBO'...
============================================================================================
USE MASTER;
GO
--
======================================================================================================
-- OWNER DE BASES DE DATOS
--
-- QUITA EL OWNER DE UN USUARIO A NIVEL
BASE DE DATOS Y LO SUSTITUYE POR 'SA'
-- Con la finalidad de poder eliminar
usuarios que son Owners DE BASES DE DATOS
-- ======================================================================================================
DECLARE @ListaBases TABLE (DataBaseName varchar (500) COLLATE SQL_Latin1_General_CP850_CI_AI);
INSERT INTO @ListaBases (DataBaseName) --values
SELECT name FROM SYS.databases where len(owner_sid)>5
DECLARE @Usuario VARCHAR (500) ;
DECLARE @DataBaseName VARCHAR (500);
DECLARE @strSQL NVARCHAR (4000);
SET @DataBaseName = ''
WHILE (@DataBaseName IS NOT NULL)
BEGIN
SET @DataBaseName = (SELECT top 1
DataBaseName FROM @ListaBases where DataBaseName >
@DatabaseName order by
DataBaseName)
IF @DataBaseName IS NOT NULL
BEGIN
SET @StrSQL = 'USE [' +
@DataBaseName + '] ';
SET @StrSQL = @StrSQL
+ 'EXEC
dbo.sp_changedbowner @loginame = N''sa'', @map = false; '
PRINT @strSQL
EXECUTE sp_executesql @strSQL;
END
END
GO
--
=====================================================================================
-- ELIMINAR EL PERMISIO DE IMPERSONATE
DESDE MASTER
--
=====================================================================================
USE MASTER;
GO
select
ID
= ROW_NUMBER() OVER (ORDER BY B.NAME)
,a.*, b.name as UserName, c.name as Impersonate
,strsql = 'REVOKE IMPERSONATE ON Login::' + b.name + ' TO ' + c.name + ';'
into #impersonate
from sys.server_permissions as a
inner join sys.server_principals as b on a.major_id = b.principal_id
inner join sys.server_principals as c on a.grantee_principal_id
= c.principal_id
where permission_name = 'impersonate'
DECLARE @ID INT
DECLARE @STRSQL NVARCHAR(500)
SET @ID = 0;
WHILE (@ID IS NOT NULL)
BEGIN
SET @ID = (SELECT TOP 1 ID FROM #IMPERSONATE WHERE
ID > @ID ORDER
BY ID)
IF @ID IS NOT NULL
BEGIN
SET @STRSQL = (SELECT STRSQL FROM
#IMPERSONATE WHERE ID =
@ID)
PRINT @STRSQL;
EXECUTE SP_EXECUTESQL @STRSQL;
END
END
DROP TABLE #IMPERSONATE
GO
-----------------------------------------------------------------------------------------------------------------------------------------------------
--
===========================================================================
--
-- OWNERS DE SCHEMAS
--
-- Cambia el propietario a dbo para
todos los esquemas con la finalidad de que
-- ningun usuario sea dueño de algún
esquema y no se pueda borrar.
--
/*
Msg 15138, Level 16, State 1, Line 281
The database principal owns a schema in
the database, and cannot be dropped.
*/
--
===========================================================================
USE DATABASE_NAME;
GO
DECLARE @ESQUEMAS AS TABLE (ESQUEMA NVARCHAR (500))
DECLARE @ESQUEMA NVARCHAR (500)
DECLARE @STRSQL NVARCHAR (MAX)
INSERT INTO @ESQUEMAS
SELECT NAME FROM SYS.SCHEMAS WHERE SCHEMA_ID <100 AND NAME NOT IN ('dbo','guest','INFORMATION_SCHEMA','sys')
SET @ESQUEMA = '';
WHILE (@ESQUEMA IS NOT NULL)
BEGIN
SET @ESQUEMA = (SELECT TOP 1 ESQUEMA FROM @ESQUEMAS WHERE
ESQUEMA > @ESQUEMA ORDER
BY ESQUEMA)
IF @ESQUEMA IS NOT NULL
BEGIN
SET @STRSQL = 'ALTER AUTHORIZATION ON
SCHEMA::[' + @ESQUEMA + '] TO [dbo];'
PRINT @STRSQL;
EXECUTE SP_EXECUTESQL @STRSQL;
END
END
GO
--
=======================================================================================================
-- AUTOR: OMAR OLGUÍN
-- FECHA: 18 MARZO 2016
-- DESCRIPCION PARA ELIMINARLOS UNO A UNO
-- SELECCIONA
SOLAMENTE USUARIOS DE BASE DE DATOS
--
--
====================================================================================================================
USE MASTER;
GO
DECLARE @ListaBases AS TABLE (DatabaseName NVARCHAR (50) )
DECLARE @ListaUsuarios AS TABLE (UserName NVARCHAR (50) )
DECLARE @DatabaseName NVARCHAR (50)
DECLARE @UserName NVARCHAR (50)
DECLARE @strSQL NVARCHAR (1000);
INSERT INTO @ListaBases
SELECT NAME FROM SYS.DATABASES WHERE
database_id>4
SET @DatabaseName = '';
WHILE (@DatabaseName IS NOT NULL)
BEGIN
SET @DatabaseName = (SELECT top 1
DatabaseName FROM @ListaBases where DatabaseName >
@DatabaseName ORDER BY
DatabaseName)
IF @DatabaseName IS NOT NULL
BEGIN
-- desconecta a
todos los usuarios excepto esta cuenta
SET @strSQL = 'USE [' + @DatabaseName + ']; ALTER DATABASE [' +
@DatabaseName + '] SET AUTO_UPDATE_STATISTICS_ASYNC ON WITH
NO_WAIT';
PRINT
@strSQL;
EXECUTE sp_executesql @strSQL;
-- desconecta a
todos los usuarios excepto esta cuenta
SET @strSQL = 'USE [' + @DatabaseName + ']; ALTER DATABASE [' +
@DatabaseName +
'] SET SINGLE_USER WITH ROLLBACK IMMEDIATE';
PRINT
@strSQL;
EXECUTE sp_executesql @strSQL;
----------------------------------------------------
IMPRIME TODAS LAS BASES DE DATOS
-- PRINT @DatabaseName;
SET @strSQL = 'USE [' +
@DatabaseName + '];';
SET @strSQL = @strSQL
+ 'SELECT NAME FROM
SYS.SYSUSERS WHERE GID = 0 AND LEN(SID)>10 AND ALTUID IS NULL AND NAME NOT
IN (''SA'', ''DBO'',''OtroUsuario'',''NOMBREDOMINIO\UsuarioDominio'');';
INSERT INTO
@ListaUsuarios-----------Agrega todos los usuarios de
la base a la variable temporal tipo tabla
EXECUTE sp_executesql @strSQL;
SET @UserName = '';
WHILE (@UserName IS NOT NULL)
BEGIN
SET @UserName = (SELECT TOP 1 UserName
FROM @ListaUsuarios WHERE
UserName > @UserName ORDER BY UserName)
IF @UserName IS NOT NULL
BEGIN
--------------------------------------------------------------
imprime todos los USUARIOS
SET @strSQL = 'USE [' +
@DatabaseName + '];';
SET @strSQL = @strSQL + 'DROP USER [' +
@UserName + '];'
PRINT @strSQL;
EXECUTE sp_executesql @strSQL;
END
END
SET @strSQL = 'USE MASTER; ALTER DATABASE [' + @DatabaseName + '] SET MULTI_USER
WITH ROLLBACK IMMEDIATE';
PRINT @strSQL;
EXECUTE sp_executesql @strSQL;
DELETE @ListaUsuarios;
END
--BREAK;
END
GO---------------------------------------------------------------------------------------------------------------------------------
--
=======================================================================================================
-- AUTOR: OMAR OLGUÍN
-- FECHA: 18 MARZO 2016
-- DESCRIPCION ELIMINA LOS LOGINS A NIVEL SERVIDOR
-- ANTES
DE ELIMINARLOS, SE DEBEN MATAR TODAS LAS SESIONES ACTIVAS.
--
--
=======================================================================================================
DECLARE @ListaUsuarios TABLE (Usuario varchar (50) COLLATE SQL_Latin1_General_CP850_CI_AI);
INSERT INTO
@ListaUsuarios (Usuario) --values
-- NO DEBE BORRAR USUARIOS DE DOMINIO
SELECT NAME FROM SYS.sySLOGINS WHERE PASSWORD IS NOT NULL AND NAME NOT IN ('SA','UserName')
DECLARE @Usuario VARCHAR (50) ;
DECLARE @Password VARCHAR (50);
DECLARE @DB VARCHAR (50);
DECLARE @strSQL NVARCHAR (4000);
select * from @ListaUsuarios
USE master;
SET @Usuario = '';
SET @DB = '';
WHILE (@Usuario COLLATE SQL_Latin1_General_CP850_CI_AI is not null)
BEGIN
SET @Usuario = (
SELECT top 1 Usuario COLLATE SQL_Latin1_General_CP850_CI_AI
from @ListaUsuarios
WHERE Usuario COLLATE SQL_Latin1_General_CP850_CI_AI> @Usuario COLLATE SQL_Latin1_General_CP850_CI_AI
order by Usuario COLLATE
SQL_Latin1_General_CP850_CI_AI
) COLLATE SQL_Latin1_General_CP850_CI_AI;
if @Usuario is not null
BEGIN
----------------------
DENIEGA Y DESHABILITA AL LOGIN
SET @strSQL = '
DENY
CONNECT SQL TO [' + @Usuario + '];
ALTER
LOGIN [' + @Usuario + '] DISABLE;'
PRINT @strSQL;
EXECUTE sp_executesql @strSQL;
--------------------- MATA TODAS LAS SESIONES EXISTENTES
QUE TENGA EL LOGIN ABIERTAS
WHILE EXISTS (
SELECT TOP 1
SESSION_ID
FROM sys.dm_exec_sessions
WHERE original_login_name =
@Usuario
)
BEGIN
SET @strSQL = (SELECT TOP 1 CAST(session_id AS varchar(5))
FROM sys.dm_exec_sessions
WHERE original_login_name =
@Usuario)
SET @strSQL = 'KILL ' + @strSQL;
PRINT @strSQL;
EXECUTE sp_executesql @strSQL;
END
--------------------------------------------------------------------------------
----------------------- ELIMINA EL LOGIN
SET @strSQL = '
REVOKE
VIEW ANY DEFINITION TO ['
+ @Usuario + '];
DROP
LOGIN [' + @Usuario + '];'
PRINT @strSQL;
EXECUTE sp_executesql @strSQL;
end
END
GO ------------------------------------------------------------------------------------------------------------------------------------------------------------
