主页 / dba / 问题 / 250844
Accepted
igelr
Asked: 2019-10-12 03:13:17 +0800 CST

如何在 SQL Server 中查找未使用的登录名？

  • 772

我的服务器上有几个数据库，每个数据库都有自己的用户。现在我需要查找并删除所有未映射到任何数据库中任何用户的登录名。但我也看到，登录可能没有映射到任何用户，但他们能够执行一些操作，因为他们具有像 sysadmin 这样的角色。因此，在这种情况下，这些登录名正在使用中。我想知道如何才能找到真正无用的登录名，而这些登录名只是被孤立而没有任何用处？

谢谢

sql-server logins

2 个回答

  1. 我有几年前做的很丑的东西。我只需要实现一个动态 SQL 来自动执行这个“选择”。好吧，这里是：

    首先创建这个报告表：

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

    然后执行此查询并运行它：

    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')

    然后你可以拥有：

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

    和

    --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

    您的情况与此相同。

    但是，以下查询可帮助您获取未映射到数据库中任何用户且未分配给服务器角色的登录名，您可以注释 (--) 子句中的最后一个谓词 ( and (r.name = 'public' or r.name is null ))where以列出所有登录名及其角色名称未与任何数据库用户映射，并FixCommand从结果中为选定的登录选择列值 (T-SQL)。

    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

相关问题