//----------------------------------------------------------------------- 
// <copyright file="ADIntegration.cs" company="SpittingCAML"> 
// Free to download and use - from blog.mgallen.com
// </copyright> 
//----------------------------------------------------------------------- 
namespace SpittingCAML.SQLCLR.DatabaseAssemblies 
{ 
    using System; 
    using System.Collections.Generic; 
    using System.Data.SqlTypes; 
    using System.DirectoryServices.AccountManagement; 
    using System.Linq; 
    using System.Text; 
    using Microsoft.SqlServer.Server; 
    using System.Data; 
    /// <summary> 
    /// this class contains the AD Integration methods (Stored Procedures) 
    /// </summary> 
    public class ADIntegration 
    { 
        /// <summary> 
        /// this method determines if a user is a member of the supplied 
        /// active directory group 
        /// </summary> 
        /// <param name="user">the user to search for</param> 
        /// <param name="domain">the domain to search</param> 
        /// <param name="groupName">the group to determine</param> 
        /// <returns>true if access granted, false if not granted</returns> 
        [Microsoft.SqlServer.Server.SqlFunction] 
        public static bool IsGroupMember(string user, string domain, string groupName) 
        { 
            bool isGroupMember = false; 
            // can't proceed with lookup if supplied parameters are null 
            if(string.IsNullOrEmpty(user) || string.IsNullOrEmpty(domain) || string.IsNullOrEmpty(groupName)) 
            { 
                return isGroupMember; 
            }           
            using (var ctx = new PrincipalContext(ContextType.Domain, domain)) 
            { 
                using (var grp = GroupPrincipal.FindByIdentity(ctx, IdentityType.Name, groupName)) 
                { 
                    isGroupMember = grp != null && 
                        grp 
                        .GetMembers(true) 
                        .Any(m => m.SamAccountName.ToLower() == user.ToLower()); 
                } 
            } 
            /* use this when returning a table 
            SqlDataRecord record = new SqlDataRecord( 
                    new SqlMetaData("Permission", SqlDbType.Bit)); 
            SqlContext.Pipe.SendResultsStart(record); 
            record.SetBoolean(0, isGroupMember); 
            SqlContext.Pipe.SendResultsRow(record); 
            SqlContext.Pipe.SendResultsEnd(); */ 
            return isGroupMember; 
        } 
    } 
}