1

I have a couple of databases on my server, each with its own users. Now I need to find and delete all those logins, which are not mapped to any user in any database. But I've also seen that it is possible that logins are not mapped to any user but they are able to do some operations because they have roles like sysadmin. So in this case those logins are in use. I want to know how can I find really not useful logins which are just left orphaned without any use?

Thanks

  • Login cannot be "orphaned", only users can be – sepupic Oct 11 '19 at 11:25
  • Thanks @sepupic, I know, but I used this term in order to represent my idea more clearly. I want those logins who are in a 'orphaned' state aka are not used by any application in any database – igelr Oct 11 '19 at 11:31
  • Some logins can be created to make certificates from them, they will not ever "log in" nieghter be mapped to databases. They may not be members of sysadmin but still they are used. They are "orphaned" or no? – sepupic Oct 11 '19 at 12:08
  • No, because it has a use case. I want completely unused logins so I can clean up my environment – igelr Oct 11 '19 at 13:48
0

Your case looks same as this.

However, following query help you to get logins that are not mapped to any user in the database and not assigned to server role, you may comment (--) the last predicate (and (r.name = 'public' or r.name is null )) in the where clause to list all logins with their role names that are not mapped with any database user, and pick FixCommand column value (T-SQL) for selected logins from result.

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
| improve this answer | |
0

I have something really ugly I made years ago. I just need to implement a dynamic SQL to automatically execute this "select". well here it goes:

First create this report table:

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

Then execute this query and run it:

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

Then you can have:

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

AND

--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
| improve this answer | |
  • I would love if someone upgrade/Implement stuff here. – Racer SQL Oct 11 '19 at 12:20
  • 2
    match users to logins by SID, not by name. It is possible to have them be "mapped" but not have the same name. – Jonathan Fite Oct 11 '19 at 12:35
  • 1
    Upvoted the comment above; besides, you can have some principals as owners of some databases, so they are mapped to dbo. They are not sysadmins, simply owners, but they cannot be defined as "orphaned" or "without any use" and your script will catch them while they are very useful logins – sepupic Oct 11 '19 at 13:22
  • 1
    Yep you guys are right. I automatically thinnk about this when using this script but it worth mentioning it. thanks. – Racer SQL Oct 11 '19 at 13:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.