Monthly Archives: October 2008

Determine AD Group membership in SQL server

Today, one of the customers I work for, asked me to review the Authentication method used in 2 applications. The group membership of the application’s users was stored in tables on a SQL Server and the application called an Stored Procedure to determine if a user was a member of a specific group by passing the username of the logged in user and groupname. To make the applications easier to maintain, the customer preferred to use Active Directory groups. As I did not want to change the code of the application (and test it, and deploy the new version), I thought about modifying the Stored Procedures so they would query the Active Directory instead of the database. As the applications use SQL Server 2000, writing a CRL routine and using that from the SQL Server was not an option. Luckily an provider exists allowing you to connect to Active Directory: the ‘OLE DB Provider for Microsoft Directory Services’. I started with adding a Linked server to the SQL server name ‘ActiveDirectory’ and defined that connections must be made by an existing user account with sufficient access rights to Active Directory. Next I played around a bit in Query Analyzer and managed to execute some simple queries on AD For example, to return all the users for the domain ‘somedomain.local’ you would execute

SELECT * FROM openquery(ActiveDirectory, ‘select ADsPath, sAMAccountName FROM ”LDAP://dc=somedomain,dc=local” where objectCategory = ”Person” and objectClass= ”user” ‘)

The openquery statement has one major drawback: you have to provide a string contstant for the query and cannot use variables. You have to use ‘sp_executesql’ instead. Also, in Active Directory, it is possible to have nested groups (i.e. a group within a group). To make sure all the users that belong to certain group are returned, a recursive method was needed. After banging my head on my desk several times to get the quoting right, I ended up with the 2 stored procedures listed below. The stored procedure ‘sp_getADGroupUsers’ expects 2 parameters:

  • @LDAPRoot: the root of the Active Directory domain. (e.g. ‘ldap://dc=somedomain,dc=local/‘)
  • @FriendlyGroupName: the name of the group to list the users for (e.g. ‘domain users’).

The basic flow of the SP is as follows:

  • Create a temp table (#ADSI) to stored the users while the other Stored Procedures is called recursively
  • Determine the full ADsPath for the specified group
  • Call ‘sp_getadgroupusers_recursive’ passing the LDAPRoot and the full ADsPath

The Stored Procedure ‘sp_getadgroupusers_recursive’ expects 2 parameters:

  • @LDAPRoot: the root of the Active Directory domain.
  • @group_name: the full ADsPath (without the LDAP:// portion) of the group to list the users for.

The basic flow of the SP is as follows:

  • Insert all the users of the specified group into the temp table (#ADSI)
  • Create a cursor with all the Groups within the specified group
  • Call ‘sp_getadgroupusers_recursive’ (itself) for each childgroup


sp_getADGroupUsers (click ‘expand source’ to open)

CREATE PROCEDURE [dbo].[sp_getADGroupUsers]
@LDAPRoot nvarchar(200),
@FriendlyGroupName nvarchar(200)


DECLARE @SQLString nvarchar(4000)
DECLARE @group_name nvarchar(500)
--Create a temp table to hold the results
ADsPath nvarchar(500),
SamAccountname nvarchar(100))

--build an SQL string to return the ADsPath for the requested group
--(the 'LDAP://' part will be stripped off)
set @SQLString = N'SELECT @group_name_int = SUBSTRING(ADsPath,8,LEN(ADsPath))
WHERE SamAccountname=''''' + @FriendlyGroupName + ''''' '')'

--Execute the statement (will return the ADsPath in @group_name
EXEC sp_executesql @SQLString,
N'@group_name_int nvarchar(200) OUTPUT',
@group_name_int=@group_name OUTPUT

--if the group_name is null, give up (raise error?)
IF @group_name is null

--recursively get all the users for the group
EXEC sp_getadgroupusers_recursive @LDAPRoot, @group_name

--return the resultset
--(use DISTINCT as a user might be a member of several groups)

--explicitely drop the temp table


sp_getadgroupusers_recursive (click ‘expand source’ to open)

CREATE PROCEDURE [dbo].[sp_getadgroupusers_recursive]
@LDAPRoot nvarchar(200),
@group_name nvarchar(500)


DECLARE @SQLString nvarchar(4000)
DECLARE @LDAPPath nvarchar(500)

--build an statement to insert all the user from the specified
--group into the temp table (temp table is created in parent sp)
SET @SQLString = N'INSERT INTO #ADSI SELECT ADsPath,SamAccountname
SamAccountname FROM ''''' + @LDAPRoot + '''''
WHERE objectCategory = ''''Person'''' and objectClass = ''''user''''
and memberOf=''''' + @group_name + ''''' '') ';

--execute the statement
EXECUTE sp_executesql @SQLString;

--declare a variable to hold a cursor (need a variable because we
--need a Local cursor since the SP is called recursively)
DECLARE @group_cursor CURSOR

--Load all the groups within the specified group into the cursor
SET @SQLString = N'SET @group_cursor = CURSOR STATIC FOR
FROM ''''' + @LDAPRoot + '''''
WHERE objectClass = ''''group''''
and memberOf=''''' + @group_name + ''''' '')
FOR READ ONLY; OPEN @group_cursor';

EXECUTE sp_executesql @SQLString, N'@group_cursor CURSOR OUTPUT',
@group_cursor OUTPUT

--loop through the Cursor (it's opened in the sp_excecutesql statement above)
--the only column in the cursor contains the ADsPath with the 'LDAP://' part
--already stripped off)

FETCH NEXT FROM @group_cursor INTO @LDAPPath
--recursive call using the 'inner group'
EXEC sp_getadgroupusers_recursive @LDAPRoot, @LDAPPath
--get next
FETCH NEXT FROM @group_cursor INTO @LDAPPath

--clean up cursor
CLOSE @group_cursor
DEALLOCATE @group_cursor

Upgrading ContentType Features

Upgrading contenttypes after they are deployed using a feature is quite a challenge. According to this MS article (, it’s a big no-no to modify the Content Type definitions after deployment.

Under no circumstances should you update the content type definition file for a content type after you have installed and activated that content type. Windows SharePoint Services does not track changes made to the content type definition file. Therefore, you have no method for pushing down changes made to site content types to the child content types.

Off course, from a developers perspective, this is major drawback because it makes tasks that should be very trivial (like adding a column) virtually impossible.

After searching around the internet for a solution on how to update content types, I stumbled upon this post on Gary Lapointe’s STSADM blog.

Gary created an STSADM extenstion based on a post by Søren Nielsen that will update a content type by comparing all the fields in the content type in the lists where the content type is used.

The code works great, but is a bit limited as you have to run the stsadm command for each ContentType that you have deployed (of course, you could script the commands).

I’ve created an SPFeatureReceiver that uses Gary’s/Søren’s code to update all the ContentTypes that are deployed using the feature.

To update the ContentTypes, I just have to re-install the (updated) feature and re-activate it.

I will post the source as soon a I find some extra spare time.

PagerRow not showing in SPGridView when setting AllowPaging=true

When you enable paging in an SPGridView, the PagerRow doesn’t show up by default. To work around this, you have to set the PagerTemplate to ‘null’ AFTER adding the control to the controls collection but BEFORE calling DataBind().


protected override void CreateChildControls() {
SPGridView grid = new SPGridView;
//set PagerTemplate to null
grid.PagerTemplate = null;
grid.PageIndexChanging +=
new GridViewPageEventHandler(grid_PageIndexChanging);

Showing the StackTrace in SharePoint

Instead of being stuck with the default errors in sharepoint (usually ‘unexpected error’), you can force SharePoint to show the StackTrack.

To do so, you must make 2 changes to the web.config file of each site where you wish to enable this.

First of all, set the ‘Mode’ value of the ‘CustomErrors’ element to ‘Off’ (or ‘RemoteOnly’)

Second, set the ‘CallStack’ value for the ‘SafeMode’ element to ‘true’

Error ‘SPSearch ([accountname]) when trying to activate WSS Search

I ran into the error shown below when I tried to activate WSS Search on my development server:  When examining the WSS Log file, I found the following error:

The call to SPSearchServiceInstance.Provision (server ‘[servername]’) failed. Setting back to previous status ‘Disabled’. System.ComponentModel.Win32Exception: SPSearch (mossadmin) at Microsoft.SharePoint.Win32.SPAdvApi32.ChangeServiceConfiguration(String strServiceName, String strAccountName, SecureString sstrPassword, IdentityType identityType, Boolean bDontRestartService) at Microsoft.SharePoint.Administration.SPProvisioningAssistant.ProvisionProcessIdentity(String strUserName, SecureString secStrPassword, IdentityType identityType, Boolean isAdminProcess, Boolean isWindowsService, String strServiceName, Boolean dontRestartService) at Microsoft.SharePoint.Administration.SPProcessIdentity.ProvisionInternal(SecureString sstrPassword, Boolean isRunningInTimer) at Microsoft.SharePoint.Administration.SPProcessIdentity.Provision() at Microsoft.SharePoint.Administration.SPWindowsServiceInstance.ProvisionCredentials() at Microsoft.SharePoint.Administration.SPWindowsServiceInstance.Provision(Boolean start) at Microsoft.SharePoint.Administration.SPWindowsServiceInstance.Provision() at Microsoft.SharePoint.Search.Administration.SPSearchServiceInstance.Provision() at Microsoft.SharePoint.Search.Internal.UI.SPSearchServiceInstanceSettings.BtnSubmit_Click(Object sender, EventArgs args)

After fiddeling around with the settings, I figured out that you must enter the name of the service and content accounts in the format [SERVERNAME]\[AccountName].

Overriding Javascript functions

Jonathan Yong has a great tutorial on how to override javascript functions: