Archive for the 'T-SQL' Category

Determining Active Directory group membership from SQL Server 2005

Have you ever needed to determine an authenticated user’s Active Directory (AD) group memberships from within SQL Server?

It makes sense if you are able to utilise AD to decide a user’s role or permissions in an application. This reduces the development time required to develop a custom permissions model, and allows the helpdesk, maintenance and operations team keep track of user privileges from one place.

It seems SQL Server 2005 does not have an out of box solution to this. Searching the various support forums and search engines lead me down the Managed .NET assembly route. Microsoft calls this building a database object with common language runtime (CLR) integration.

You may be asking yourselves… why not go down the SQL Server Integration Services route to gather an export from Active Directory… why would you query LDAP each time a query in SQL Server is executed? These are important design decisions that are really down to the required functionality.

You can find out another way to achieve this here: How To Get Active Directory Users and Groups Using SQL Server Integration Services SSIS 2005

Ok, so I am now going to build a CLR assembly in Visual Studio 2008 with the target framework set to .NET 3.5. SQL Server 2005 is actually .NET 2.0 targeted, but 3.5 uses the same CLR engine, so we are able to make use of things like LINQ and additional Active Directory components. [more on this later].

   1: namespace SpittingCAML.SQLCLR.DatabaseAssemblies 

   2: { 

   3:     using System; 

   4:     using System.Collections.Generic; 

   5:     using System.Data.SqlTypes; 

   6:     using System.DirectoryServices.AccountManagement; 

   7:     using System.Linq; 

   8:     using System.Text; 

   9:     using Microsoft.SqlServer.Server; 

  10:     using System.Data; 

  11:     /// <summary> 

  12:     /// this class contains the AD Integration methods (Stored Procedures) 

  13:     /// </summary> 

  14:     public class ADIntegration 

  15:     { 

  16:         /// <summary> 

  17:         /// this method determines if a user is a member of the supplied 

  18:         /// active directory group 

  19:         /// </summary> 

  20:         /// <param name="user">the user to search for</param> 

  21:         /// <param name="domain">the domain to search</param> 

  22:         /// <param name="groupName">the group to determine</param> 

  23:         /// <returns>true if access granted, false if not granted</returns> 

  24:         [Microsoft.SqlServer.Server.SqlFunction] 

  25:         public static bool IsGroupMember(string user, string domain, string groupName) 

  26:         { 

  27:             bool isGroupMember = false; 


  29:             // can't proceed with lookup if supplied parameters are null 

  30:             if(string.IsNullOrEmpty(user) || string.IsNullOrEmpty(domain) || string.IsNullOrEmpty(groupName)) 

  31:             { 

  32:                 return isGroupMember; 

  33:             }           

  34:             using (var ctx = new PrincipalContext(ContextType.Domain, domain)) 

  35:             { 

  36:                 using (var grp = GroupPrincipal.FindByIdentity(ctx, IdentityType.Name, groupName)) 

  37:                 { 

  38:                     isGroupMember = grp != null && 

  39:                         grp 

  40:                         .GetMembers(true) 

  41:                         .Any(m => m.SamAccountName.ToLower() == user.ToLower()); 

  42:                 } 

  43:             } 


  45:             return isGroupMember; 

  46:         } 

  47:     } 

  48: } 

Code Snippet 1 – the assembly that will be built into a DLL in Visual Studio 2008 (download a copy here: ADIntegration.cs)

The code snippet creates a class ‘ADIntegration’ in the ‘SpittingCAML.SQLCLR.DatabaseAssemblies’ namespace. This is important to remember, as it is required later when registering the assembly.

The functionality of this method is as follows:

  • User calls the method with the user name, domain and user group as parameters
  • System determines if supplied user is a member of the supplied user group for the supplied domain
  • System returns boolean true if user is a member and boolean false if not a member.
  • The method code is shamelessly stolen from StackOverflow, user huseyint. The rest is down to the style conventions of my organisation.

I have tagged the method ‘IsGroupMember’ with the ‘[Microsoft.SqlServer.Server.SqlFunction] tag to let SQL server know this is intended to be used in place of a typical SQL Server function.

One your assembly is built, it is almost ready to be put into a database of your choice, as an object. You’ll notice we make use of LINQ and Directory Services. However, since SQL Server 2005 is targeting .NET 2.0 it is necessary for you to install some pre-requisites in order to gain access to .NET 3.5 features. It should also be noted that not all .NET 2.0 features are available in SQL Server 2005, as the code has to meet certain criteria to be included in the ‘safe’ category.

Read more about safe, unsafe and external access assemblies: here.

The pre-requisites required are:

  • CLR enabled must be set on your database server – this enables CLR assemblies
  • The target database must be set to TRUSTWORTHY to allow UNSAFE assemblies to be run against it.
  • System.Core [.NET 3.5 core components]
  • System.DirectoryServices.AccountManagement [.NET 3.5 Active Directory specific methods]
  • System.DirectoryServices.Protocols [.NET 2.0 dependancy of System.DirectoryServices.AccountManagement]
  • System.DirectoryServices [.NET 2.0 dependancy of System.DirectoryServices.AccountManagement]

The pre-requisites are typically found in the install folders for .NET 2.0 and .NET 3.5

   1: -- must enable CLR on the database


   3: EXEC sp_configure 'show advanced options' , '1';

   4: go

   5: reconfigure;

   6: go

   7: EXEC sp_configure 'clr enabled' , '1'

   8: go

   9: reconfigure;


  11: DECLARE @dbName NVARCHAR(255)

  12: SET @dbName = '<database name here>'

  13: DECLARE @sql NVARCHAR(4000)


  15: SET @sql = 'USE ' + @dbName;


  17: -- must make database trustworthy


  19: EXEC sp_executesql @sql

  20: EXEC sp_changedbowner 'sa'  

  21: SET @sql = 'ALTER DATABASE ' + @dbName + ' SET TRUSTWORTHY ON;'

  22: EXEC sp_executesql @sql


  24: GO


  26: DECLARE @DotNet2Folder NVARCHAR(1000)

  27: DECLARE @DotNet35Folder NVARCHAR(1000)

  28: DECLARE @path NVARCHAR(4000)


  30: SET @DotNet2Folder = 'C:\Windows\Microsoft.NET\Framework\v2.0.50727'

  31: SET @DotNet35Folder = 'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.5'



  34: --if the [System.Core] assembly exists then drop it 

  35: IF EXISTS (SELECT * FROM sys.assemblies WHERE [name] = 'System.Core') 

  36:         DROP ASSEMBLY [System.Core] 


  38: --if the [System.DirectoryServices.AccountManagement] assembly exists then drop it 

  39: IF EXISTS (SELECT * FROM sys.assemblies WHERE [name] = 'System.DirectoryServices.AccountManagement') 

  40:         DROP ASSEMBLY [System.DirectoryServices.AccountManagement] 


  42: --if the [System.DirectoryServices.Protocols] assembly exists then drop it 

  43: IF EXISTS (SELECT * FROM sys.assemblies WHERE [name] = 'System.DirectoryServices.Protocols') 

  44:         DROP ASSEMBLY [System.DirectoryServices.Protocols] 


  46: --if the [System.DirectoryServices] assembly exists then drop it 

  47: IF EXISTS (SELECT * FROM sys.assemblies WHERE [name] = 'System.DirectoryServices') 

  48:         DROP ASSEMBLY [System.DirectoryServices] 


  50: CREATE ASSEMBLY [System.Core]  

  51: FROM @DotNet35Folder + '\system.core.dll'  

  52: WITH permission_set = UNSAFE  


  54: CREATE ASSEMBLY [System.DirectoryServices.Protocols]  

  55: FROM @DotNet2Folder + '\System.DirectoryServices.Protocols.dll'  



  58: CREATE ASSEMBLY [System.DirectoryServices.AccountManagement]

  59: FROM @DotNet35Folder + '\System.DirectoryServices.AccountManagement.dll'

  60: WITH permission_set = UNSAFE  


  62: CREATE ASSEMBLY [System.DirectoryServices]  

  63: FROM @DotNet2Folder + '\System.DirectoryServices.dll'  



  66: GO  

Code Snippet 2: running the above T-SQL script will enable CLR on the database server, ensure the database you are going to be using assemblies in is trustworthy and registers the pre-requisite components. (download a copy here: ActiveDirectory_PreReqs.sql)

If you make use of the code snippet you’ll need to supply your database name and the locations of the .NET 2.0 and .NET 3.5 DLLs. The pre-requisites are installed in UNSAFE mode since they contain code that is not certified safe. If you try to register the assemblies as SAFE you will be presented with an error showing the areas of the code that are not certified safe.

We are now ready to deploy our assembly that contains the code in Code Snippet 1.

   1: DECLARE @dbName NVARCHAR(255)

   2: SET @dbName = '<database name here>'


   4: DECLARE @assemFolder NVARCHAR(1000)

   5: SET @assemFolder = '<assembly folder here>'


   7: -- have to drop the function first because you can't delete the assembly

   8: -- when the function is dependant on it 


  10:        FROM   sysobjects 

  11:        WHERE  name = N'udf_IsGroupMember')

  12:     DROP FUNCTION dbo.[udf_IsGroupMember]


  14: --if the [SpittingCAML.SQLCLR.DatabaseAssemblies] assembly exists then drop it 

  15: IF EXISTS (SELECT * FROM sys.assemblies WHERE [name] = 'SpittingCAML.SQLCLR.DatabaseAssemblies') 

  16:         DROP ASSEMBLY [SpittingCAML.SQLCLR.DatabaseAssemblies] 


  18: CREATE ASSEMBLY [SpittingCAML.SQLCLR.DatabaseAssemblies]  

  19: FROM @assemFolder + '\SpittingCAML.SQLCLR.DatabaseAssemblies.dll'  

  20: WITH permission_set = unsafe  


  22: GO  


  24: /********************************************************************************

  25: *

  26: * Name: [udf_IsGroupMember]

  27: *

  28: *

  29: * Description: 

  30: *

  31: *       Function determines if the supplied user is in an active directory group

  32: *     

  33: *        NB: this is a MANAGED CODE function utilising the 

  34: *                   SpittingCAML.SQLCLR.DatabaseAssemblies assembly class

  35: *

  36: * Revision History:

  37: *

  38: * Version Date         Author(s)        Description

  39: * 1.0     08 Dec 2009  Martin Allen        Intial Release

  40: *

  41: ********************************************************************************/

  42: CREATE FUNCTION [udf_IsGroupMember]

  43: (

  44:  @user nvarchar(1000), 

  45:  @domain nvarchar(1000), 

  46:  @groupName nvarchar(1000)

  47: )


  49: AS EXTERNAL NAME [SpittingCAML.SQLCLR.DatabaseAssemblies].[SpittingCAML.SQLCLR.DatabaseAssemblies.ADIntegration].[IsGroupMember]


  51: GO

Code Snippet 3: Deploying our assembly and creating a stub that will allow standard SQL stored procedures and functions call our assembly method as if it was a standard SQL function. (download a copy here: Deploy_Assembly.sql)

You will need to edit code snippet 3 with the database name and the folder that you have built the assembly [created by Code Snippet 1] to.

Once you have run this script, you are in the position to test it.

   1: DECLARE @user nvarchar(1000)

   2: DECLARE @domain nvarchar(1000)

   3: DECLARE @groupName nvarchar(1000)


   5: -- TODO: Set parameter values here.


   7: set @user = 'testuser'

   8: set @domain = 'mydomain'

   9: set @groupName = 'mygroup'


  11: SELECT [dbo].[udf_IsGroupMember](@user, @domain, @groupName)

Code Snippet 4: testing the deployment was successful. (download a copy here: Test_Assembly.sql)

Set the parameters to something more meaningful to your Active Directory, and supply a valid domain and give it a go.

Now we have a useful function to utilise alongside standard T-SQL functions. I use it to replace legacy function calls to proprietary permission models to reduce maintenance costs!

Hope this helps you out! Enjoy Smile


Further reading on SQL Server Assemblies

Massive SharePoint config database log file

Its a common problem that many of us have. You set up your SharePoint farm, and its been running sweet as a nut, till you get asked one difficult question by the server team… why is the SharePoint configuration database log file so large?

SharePoint 2007 creates its databases in FULL RECOVERY mode. This is presumably because the SharePoint central administration pages were going to offer a more comprehensive recovery option, however I’ve only ever seen a full database restore. However, even though the built in GUI doesn’t make the use of the FULL RECOVERY model, I don’t think we should discount it.

There are two (nice) ways of coping with the log growth issue;

  1. You use your own backup strategy [NB this means you can’t use the built in restore option anymore], e.g. Scheduled SQL Job to backup the databases and logs yourself. When you do this, you have the option to use the WITH TRUNCATE ONLY option, or…
  2. Use the built in SharePoint backup/restore service from Central Admin, and schedule log truncations at defined intervals. (see below snippet of code shamelessly stolen from the Microsoft DBCC Shrink file tutorial)

– Example truncating a config db logfile to 70 MB):
BACKUP LOG [Sharepoint_Config] TO DISK=’drive:\path\Yourbackupfile.bak’
USE [SharePoint_Config]
DBCC SHRINKFILE (N’SharePoint_Config_log’ , 70)

– Note: the 70 is the 70MB.  

There is an excellent article on Death By Patterns website about log file management. This gives you an overview of why logs grow, and how they work in practice.

It should be noted that in more than a few places on the net, people recommend changing the database mode from FULL to SIMPLE in order to perform a DBCC SHRINKFILE. Whilst this is a simple and effective solution it takes away a crucial service that administrators can perform on a live SharePoint farm, if something goes wrong during the day, before a backup takes place, you have the capability of doing a in place transaction rollback.

See Sherin George’s blog for an excellent overview of the recovery models available in SQL Server.

See Server Fault discussion on SharePoint recovery models.


Dead SQL Server 2005 box… could it be down to a cursor?

Today was an odd one… a colleague and I were doing some eXtreme Programming (XP)…. i.e. we needed to solve an issue with one of our many ASP.NET applications quickly!!

We were looking at a stored procedure that contained a cursor. We then fiddled a bit and commented out lots of it out to aid our debug… In our excitement to solve the problem we made the schoolboy error of not un-commenting the CLOSE and DEALLOCATE steps in the cursor when we reinstated it.

The web page linked to the stored procedure was loading in the browser… it took an age to appear… alarm bells were ringing! What had we done… was there an infinite loop cursor in progress?

I logged into SQL Server Management Studio to run a diagnostic query. The diagnostic query I was running was very similar to the excellent one Glenn Berry has published. It checked for obvious issues such as

  1. High CPU load operations
  2. Blocking queries
  3. Transaction log size
  4. Network and I/O issues

I was not unsurprised to see that the transaction log on tempdb was full. Not being a SQL expert in the slightest, I called the Operations Manager over for assistance. Remote Desktop to the machine was also non functioning… what had we done! Could a messed up cursor really cause all these issues?

Well, the investigations continue. If you experience similar issues to this, definitely look at Glen Berry’s SQL script and the following Microsoft support articles

  1. Causes of Transaction log expansion
  2. Unexpected transaction log growth



T-SQL Helper Tables… and generating dynamic email bodies… and lots more

While searching for a neat way to build the body of my emails for a particular application from database data I stumbled across a fantastic article by Robyn Page and Phil Factor (aka Database Mole). The article is entitled ‘The Helper Table Workbench’ and details how using a simple table filled with numbers from 1 to 10,000 can really help reduce the use of CURSOR and other slow T-SQL commands.

The section that interested me the most is by using this helper table it makes building the body of your email using T-SQL a piece of cake.

The function below that uses these principles (of the helper table) to do macro substitution of values into a string. It will work for replacing XHTML placeholders with a value, or producing error messages in a variety of languages. In fact, uses keep popping up for this sort of function. In this version, you are able to specify what strings are used for substitutions (the default is %1, %2, %3 etc,) and what you use as the delimiter of your list of macros and values.

CREATE FUNCTION [dbo].[ufsSubstitute]
@Template VARCHAR(8000),
@macroList VARCHAR(8000),
@valueList VARCHAR(8000),
@Delimiter VARCHAR(255)
DECLARE @macros
     (MyID INT IDENTITY(1,1),
    variable VARCHAR(80))
DECLARE @values
    (MyID INT IDENTITY(1,1),
    [value] VARCHAR(8000))
--get all the variables
INSERT INTO @macros (variable)
             number) - number)
FROM Numbers
WHERE number <= LEN(@MacroList)
    AND SUBSTRING(@Delimiter + @MacroList,
         number, LEN(@delimiter))
             = @Delimiter
ORDER BY number 

INSERT INTO @values ([value])
    SELECT SUBSTRING(@ValueList+@Delimiter, number,
         @ValueList+@Delimiter, number)
             - number)
FROM Numbers
WHERE number <= LEN(@ValueList)
AND SUBSTRING(@Delimiter + @ValueList, number,
        LEN(@delimiter)) = @Delimiter
ORDER BY number 

SELECT @Template=
                '%'+CAST(v.MyID AS VARCHAR)),
FROM @values v
LEFT OUTER JOIN @macros m ON v.MyID=m.MyID
        CAST(v.MyID AS VARCHAR))

RETURN (@Template) 


It is possible to call this T-SQL function in many ways, using templates, macro lists, value lists and delimiters. See the examples below:

SELECT dbo.ufsSubstitute (NULL,NULL,'',',')
SELECT dbo.ufsSubstitute ('','','',',')
SELECT dbo.ufsSubstitute (
    ' views','','6',',') 

SET @K2String = 'Spitting,' + 'CAML,'
+ @ApproveDocument
+ ',' + @DestinationUser + ','
 + GETDATE() + ',' + @TaskListURL + ','
+ @ViewFlowURL + ',' + @MOSSUrl

SELECT dbo.ufsSubstitute ('
Dear $1 $2,
Your K2 task $3, has been actioned by $4
to on £$5.
Please check your K2 tasklist, $6, view flow $7 or
 MOSS Site $8 which shows all open tasks
that are available to you',
SELECT dbo.ufsSubstitute ('To @Destination;
Report dated @Date
The @table table is now @rows long.
please @action'
'Phil Factor|12 Apr 2007|Log|1273980|truncate','|')
SELECT dbo.ufsSubstitute (
'I thought that your present of a %1 was %2.
Thank you very much.
The %1 will come in handy for %3'
    ,'trowel|absolutely wonderful|gardening','|') 

This is going to prove very valuable to allow me to build emails on the fly for many applications including hopefully the K2 [blackpearl] email bodies.



You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.