Wednesday, September 3, 2008

Detectando logins huérfanos.

Un script que puede ser útil para quienes tienen servidores SQL 2005 y que necesitan saber rápidamente si tienen en sus servidores logins que ya nadie los ocupa, es decir, no están como usuarios en ninguna base de datos, no están como local user en ningún linked server, no pertenecen a ningún rol de servidor y están habilitados.

Aquí está el código:

DECLARE @dbname2 varchar(40);
DECLARE dbname CURSOR FOR select name from master.sys.databases';

OPEN dbname
FETCH NEXT FROM dbname
INTO @dbname2

create table #nombres(
nombre varchar (128))

WHILE @@FETCH_STATUS = 0
BEGIN
exec('SET NOCOUNT ON;
use [' + @dbname2 + ']
INSERT #nombres
select b.name
from sys.database_principals a inner join sys.server_principals b on a.sid=b.sid
where a.type_desc <> ''DATABASE_ROLE'' and a.name <> ''dbo''
and a.name <> ''guest'' and a.name <> ''INFORMATION_SCHEMA'' and a.name <> ''sys'' ')
FETCH NEXT FROM dbname INTO @dbname2
END

CLOSE dbname
DEALLOCATE dbname

DECLARE users CURSOR FOR select distinct(nombre) from #nombres order by nombre;
declare @usuario varchar(128);

OPEN users
FETCH NEXT FROM users
INTO @usuario

create table #sids(
sid varbinary(256),
nombre varchar(128) )

WHILE @@FETCH_STATUS = 0
BEGIN
exec('SET NOCOUNT ON;
INSERT #sids
select suser_sid('''+ @usuario +'''),'''+@usuario+'''')
FETCH NEXT FROM users INTO @usuario
END

CLOSE users
DEALLOCATE users

select a.name as login
from sys.server_principals a left join #sids b on a.sid=b.sid
where b.sid is null and a.type_desc <>'CERTIFICATE_MAPPED_LOGIN' and a.type_desc <>'SERVER_ROLE' and a.name <> 'sa'
and a.name <>'BUILTIN\Administrators' and a.name <>'NT AUTHORITY\SYSTEM' and a.principal_id<>259
and a.principal_id not in (select distinct(local_principal_id) from sys.linked_logins) and a.is_disabled=0
and a.principal_id not in (select member_principal_id from sys.server_role_members)
order by a.name

drop table #nombres
drop table #sids

No comments: