Have Geek Brain

How to find out if sql server account has any user mapped using SMO

I need to write a function to remove a database login if it doesn't have any users to map using SQL Server Management Objects (SMO). How can I achieve this?

Also how to add that when using login.EnumDatabaseMappings () when there are no users logged in, null is returned. So you cannot use something like login.EnumDatabaseMappings (). Length should rather be used

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


a source to share


2 answers


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 are looking for.

+1


a source


Give it away.If you comment out the code about the cursor and look at the result of the select statement, you can see which logins it wants to delete.



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
0


a source






More articles: