List Domain Logins RRS feed

  • Question

  • I need a list of user logins in the form [domain\login]

    I don't mind if I get this through a SQL query or through .net

    The reason for this requirement is that I am creating a front end to SQL Server that will allow a super to grant other users access to SQL and to a specific database.

    I can return a list of user names from the domain.
    But CREATE LOGIN and CREATE USER require user ids in the form [domain\login] from the domain.
    I can get the current user domain\login in this form, what what I need is a list of logins for the whole domain.

    Tuesday, November 25, 2008 2:36 PM


  • Worked out the answer myself in the end. I know this is vb.net code and this is a SQL forum, but we have been able to include .net code in our SQL solutions for since SQL Server 2005!

    This vb.net routine will return a list of logins for the DomainName and format them in the manner recognised by SQL
    The directory entry object has lots of properties, I could not find these documented anywhere, but by iterating through them and examing their values I was able to find out which contained the data I needed.
    In case it is any use to anyone else, this code includes a couple of the other properties for DirectoryEntry object too.

        Friend Shared Function DomainLoginsList(ByVal DomainName As String, Optional ByVal NewStyleUserName As Boolean = False) As ArrayList

            Dim entry As DirectoryEntry = New DirectoryEntry("LDAP://" & DomainName)
            Dim  dirctrySearcherAs DirectorySearcher = New DirectorySearcher(entry)
            Dim LoginList As New ArrayList

            Dim Output As String = "" 'debug
            Dim DomainNameName As String = My.User.Name.Substring(0, My.User.Name.IndexOf("\") + 1)

            ' Apply filter for Directory Searcher
            mySearcher.Filter = ("(objectClass=user)")
            ' loop through all the users found
            For Each resEnt As SearchResult In mySearcher.FindAll()
                    ' for the current entry, get the properties
                    Dim dirctryEntry As DirectoryEntry = resEnt.GetDirectoryEntry()
                    ' do some checking and make sure the object's DisplayName attribute satisfies certain criteria
                    ' you can omit this section easily if it's not required in your situation
                    If dirctryEntry.Properties("DisplayName").Value.ToString.Contains("$") = False _
                        AndAlso dirctryEntry.Properties("DisplayName").Value.ToString.Contains("Admin") = False _
                        AndAlso dirctryEntry.Properties("DisplayName").Value.ToString.Contains("admin") = False _
                        AndAlso dirctryEntry.Properties("DisplayName").Value.ToString.Contains("Test") = False _
                        AndAlso dirctryEntry.Properties("DisplayName").Value.ToString.Contains("test") = False _
                        AndAlso dirctryEntry.Properties("DisplayName").Value.ToString.Contains("Service") = False _
                        AndAlso dirctryEntry.Properties("DisplayName").Value.ToString.Contains("service") = False _
                        AndAlso dirctryEntry.Properties("DisplayName").Value.ToString.Contains("template") = False _
                        AndAlso dirctryEntry.Properties("DisplayName").Value.ToString.Contains("Template") = False _
                        AndAlso dirctryEntry.Properties("DisplayName").Value.ToString.Contains("User") = False _
                        AndAlso dirctryEntry.Properties("DisplayName").Value.ToString.Contains("Worker") = False _
                        AndAlso dirctryEntry.Properties("DisplayName").Value.ToString.Contains("Credentials") = False _
                        AndAlso dirctryEntry.Properties("DisplayName").Value.ToString.Contains("CN=") = False _
                        AndAlso dirctryEntry.Properties("DisplayName").Value.ToString.Contains("Account") = False _
                        AndAlso dirctryEntry.Properties("DisplayName").Value.ToString.Contains("System") = False Then

                        ' all the above criteria have been met so add the DisplayName attribute to the array

                        'de.Properties("DisplayName").Value.ToString() gives the full name of the user

                        If NewStyleUserName = False Then
                            'sAMAccountName Attribute
                            'The logon name used to support clients and servers running older versions of the
                            'operating system, such as Windows NT 4.0, Windows 95, Windows 98, and LAN Manager.                 'This attribute must be less than 20 characters to support older clients
                                'SQL users a logins in format [DomainName\sAMAccountName]
                            LoginList.Add(DomainNameName & dirctryEntry.Properties("sAMAccountName").Value.ToString())
                'This returns the active directory user name format user@domain.com
                'This is not recognised by sql server
                        End If

                    End If
                Catch ex As Exception
                    ' put custom exception handling here if you need it
                    ' for example (this will cause quite a lot of extra output):
                    ' Console.WriteLine(ex.Message.ToString())
                End Try

            Return LoginList

        End Function

    I will pass one of the output logins to SQL using code like this to grant the user access to SQL and to the Database

    Use Master

    CREATE LOGIN [domain\login] FROM WINDOWS;


    use MyDatabaseName


    CREATE USER [domain\login]

    Tuesday, November 25, 2008 4:22 PM