Database

Imports System
Imports System.Text
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports System.Collections
Imports System.DirectoryServices
Imports Microsoft.SqlServer.Server

Partial Public Class UserDefinedFunctions

#Region "Constants"
    ''' <summary>
    ''' The connection string for Active Directory.
    ''' </summary>
    'Private Const LDAP_CONNECTION_STRING As String = "LDAP://<My LDAP connection string>

    ''' <summary>
    ''' The LDAP search filter need to find a user in Active Directory.
    ''' </summary>
    'Private Const LDAP_SEARCH_FILTER_USER As String = "(&(objectclass=user)(objectcategory=person)(sAMAccountName={0}))"
#End Region

    ''' <summary>
    ''' Gets all active directory groups for the user.
    ''' </summary>
    ''' <returns>All dataset permissions for the user.</returns>
    <Microsoft.SqlServer.Server.SqlFunction(DataAccess:=DataAccessKind.None, FillRowMethodName:="udfUserActiveDirectoryGroupsFill", TableDefinition:="GroupID NVARCHAR(100)")> _
    Public Shared Function udfUserActiveDirectoryGroups(ByVal userName As String) As IEnumerable

        ' Setup the active directory search.
        Dim searcher As New DirectorySearcher(LDAP_CONNECTION_STRING)
        searcher.Filter = String.Format(LDAP_SEARCH_FILTER_USER, userName)
        searcher.SearchScope = SearchScope.Subtree
        searcher.PropertiesToLoad.Add("distinguishedname")

        ' Run the active directory search. 
        Dim result As SearchResult = searcher.FindOne()
        Dim userEntry As DirectoryEntry = result.GetDirectoryEntry()

        Dim userGroups As New ArrayList
        GetActiveDirectoryGroupsForEntry(userEntry, userGroups)

        Return userGroups
    End Function


    Public Shared Sub udfUserActiveDirectoryGroupsFill(ByVal source As Object, ByRef GroupID As SqlChars)
        GroupID = New SqlChars(CType(source, String))
    End Sub



    ''' <summary>
    ''' Recursively gets the active directory groups for the directory entry.
    ''' </summary>
    ''' <param name="entry">The active directory entry.</param>
    ''' <param name="groups">The list of groups.</param>
    Private Shared Sub GetActiveDirectoryGroupsForEntry(ByVal entry As DirectoryEntry, ByVal groups As ArrayList)
        For i As Integer = 0 To entry.Properties("memberOf").Count - 1
            Dim memberEntry As New DirectoryEntry("LDAP://" + entry.Properties("memberOf")(i).ToString())

            groups.Add(memberEntry.Properties("sAMAccountName")(0).ToString())
            GetActiveDirectoryGroupsForEntry(memberEntry, groups)
        Next
    End Sub

End Class