How to find out whether sql server login has any user mapped to using SMO.

Question

I need to write a function to delete the login in the database if it does not have any users to map to using SQL Server Management Objects (SMO). How can I achieve this ?

Just like to add that when using the login.EnumDatabaseMappings(),when there are no users mappped to the login , will return null.So you can not use something like login.EnumDatabaseMappings().Length rather you should use

    mylogin = server.Logins(loginName)
    If Not mylogin Is Nothing Then
        If Not mylogin.EnumDatabaseMappings() Is Nothing Then
            mylogin.Drop()
        End If
    End If

Answer 1

How about this:

Server server = new Server("your server name");

foreach (Login login in server.Logins)
{
    DatabaseMapping[] mappings = login.EnumDatabaseMappings();
}

Should work and give you what you're looking for.

Answer 2

Give this a go, If you comment out the code about the cursor and look at the result of the select statement you can see what logins it wants to drop.

USE MASTER; 
GO

DECLARE @loginName varchar(max)
DECLARE @SQL varchar(max)

CREATE TABLE #dbusers ( 
  sid VARBINARY(85)) 

EXEC sp_MSforeachdb 
  'insert #dbusers select sid from [?].sys.database_principals where type != ''R''' 

DECLARE loginCursor CURSOR FOR

SELECT name 
FROM   sys.server_principals 
WHERE  sid IN (SELECT sid 
               FROM   sys.server_principals 
               WHERE  TYPE != 'R' 
                      AND name NOT LIKE ('##%##') 
               EXCEPT 
               SELECT DISTINCT sid 
               FROM   #dbusers) 
AND type_desc = 'SQL_LOGIN'

OPEN loginCursor  
FETCH NEXT FROM loginCursor into @loginName   
WHILE @@FETCH_STATUS=0
BEGIN
    SET @SQL = 'DROP LOGIN '+@loginName
    EXEC sp_executesql @SQL
END
CLOSE loginCursor
DEALLOCATE loginCursor

GO 
DROP TABLE #dbusers