Início / dba / Perguntas / 250844
Accepted
igelr
Asked: 2019-10-12 03:13:17 +0800 CST

Como encontrar logons não utilizados no SQL Server?

  • 772

Eu tenho alguns bancos de dados no meu servidor, cada um com seus próprios usuários. Agora preciso encontrar e excluir todos esses logins, que não estão mapeados para nenhum usuário em nenhum banco de dados. Mas também vi que é possível que os logins não sejam mapeados para nenhum usuário, mas possam fazer algumas operações porque têm funções como sysadmin. Portanto, neste caso, esses logins estão em uso. Eu quero saber como posso encontrar logins realmente inúteis que são deixados órfãos sem nenhum uso?

Obrigado

sql-server logins

2 respostas

  1. Eu tenho algo muito feio que fiz anos atrás. Só preciso implementar um SQL dinâmico para executar automaticamente esse "select". bem aqui vai:

    Primeiro crie esta tabela de relatório:

    CREATE TABLE #REPORT ( [DATABASE] VARCHAR (MAX),[USER] VARCHAR (MAX))

    Em seguida, execute esta consulta e execute-a:

    select 'use ['+name+'] '+char(13)+                                                       
'insert into #report                                                                     
            select db_name(db_id()) collate sql_latin1_general_cp1_ci_as as [database] , 
                   name collate sql_latin1_general_cp1_ci_as as [user]                   
    from sys.database_principals                                                         
        where type in (''s'',''u'')                                                      
            and name not in                                                              
                (''dbo'',''guest'',''information_schema'',''sys'')'+char(13)+char(13)    
from sys.databases                                                                       
    where name not in ('master','model','msdb','tempdb')

    Então você pode ter:

    --login without user
select name from sys.server_principals
    where name not in 
        (select [user] from #report)
        order by 1

    E

    --user without login
select [database], [user] collate latin1_general_ci_as 
    from #report
        where [user] not in 
            ( select name collate latin1_general_ci_as
                from sys.server_principals)
order by 1
    • 0
  2. Best Answer

    Seu caso se parece com este .

    No entanto, a consulta a seguir ajuda você a obter logins que não são mapeados para nenhum usuário no banco de dados e não atribuídos à função de servidor, você pode comentar (--) o último predicado ( and (r.name = 'public' or r.name is null )) na wherecláusula para listar todos os logins com seus nomes de função que não são mapeados com nenhum usuário de banco de dados e escolhem o FixCommandvalor da coluna (T-SQL) para logins selecionados do resultado.

    Declare @TSQL varchar(1000);
Declare @TSQLoop varchar(1000);
Declare @DBName varchar(128);
Declare @TargetDBs table (DBName varchar(128));
Declare @Temp Table (sid varbinary(85), name nvarchar (100) );

set @TSQL = 
'select sid, name
from sys.database_principals as dp
where type in (''S'', ''U'', ''G'') and sid is not null and (dp.name not in (''dbo'', ''guest'') and dp.name not like ''##%'' ) ';


Insert into @TargetDBs select name from sys.databases;

while exists (select * from @TargetDBs)
    BEGIN
        SET @DBName = (SELECT TOP 1 DBName from @TargetDBs);
        SET @TSQLoop = ('Use ' + @DBName + '; ') + @TSQL;

        Insert into @Temp (sid, name)
        Exec (@TSQLoop);

        DELETE FROM @TargetDBs where DBName = @DBName;
    END

    select   
            sp.name as LoginName,
            r.name as RoleName,
            sp.sid, 
            sp.type_desc,
            'drop Login [' + sp.name + ']' as FixCommand 
    from sys.server_principals as sp
        left outer join @Temp as dp on sp.sid = dp.sid
        left outer join sys.server_role_members rm on sp.principal_id = rm.member_principal_id
        left outer join sys.server_principals r on rm.role_principal_id = r.principal_id
    Where   sp.type in ('S', 'U', 'G') 
            and (not sp.name = 'sa' and sp.name not like '##%' and sp.name not like 'NT %')
            and dp.name is null 
            and (r.name = 'public' or r.name is null )
go
    • 0

relate perguntas