Extract user details from Active Directory and Store it in the database table

The following T-SQL stored procedure extracts user details from Active Directory and stores it in the database table:

CREATE TABLE [dbo].[tblORG_AD_Users](
 [sAMAccountName] [nvarchar](256) NULL
 ,[userPrincipalName] [nvarchar](256) NULL
 ,[adspath] [nvarchar](400) NOT NULL
 ,[Department] [nvarchar](256) NULL
 ,[Title] [nvarchar](256) NULL
 ,[mail] [nvarchar](256) NULL
 ,[telephoneNumber] [nvarchar](256) NULL
 ,[sn] [nvarchar](256) NULL
 ,[givenName] [nvarchar](256) NULL
 ,[cn] [nvarchar](256) NULL
 ,[l] [nvarchar](256) NULL
 ,[streetAddress] [nvarchar](256) NULL
 ,[userAccountControl] [int] NULL
 ,[loginName] [nvarchar](256) NULL
 ,[DateCreated] [datetime] NULL
) ON [PRIMARY]
GO

-- Create Object: LinkedServer [adsi]
EXEC master.dbo.sp_addlinkedserver @server = N'adsi', @provider=N'ADSDSOObject'
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_NULLS ON
GO

PRINT 'drop procedure dbo.udpGet_ORG_LDAP_Users'
GO

if exists (select * from dbo.sysobjects
where id = object_id(N'dbo.udpGet_ORG_LDAP_Users')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure dbo.udpGet_ORG_LDAP_Users
GO

PRINT 'CREATE PROC dbo.udpGet_ORG_LDAP_Users'
GO

/******************************************************************************
NAME: udpGet_ORG_LDAP_Users

PURPOSE: Gets users from Active Directory across DET and populates to
the tblORG_AD_Users table

PARAMETERS: @pdcName - Domain (e.g 'org.com.win')
CALL SAMPLE: exec dbo.udpGet_ORG_LDAP_Users null
NOTE: userAccountControl can be use to filter enabled account

(use WHERE userAccountControl & 2 = 0 clause in the query)

REVISIONS:
$Log$
Ver Date Author Description
******************************************************************************/
CREATE PROC dbo.udpGet_ORG_LDAP_Users
@pdcName nvarchar
AS
 BEGIN
  DECLARE @sAMAccountName nvarchar(256)
   ,@userPrincipalName nvarchar(256)
   ,@adspath nvarchar(400)
   ,@Department nvarchar(256)
   ,@Title nvarchar(256)
   ,@mail nvarchar(256)
   ,@telephoneNumber nvarchar(256)
   ,@sn nvarchar(256)
   ,@givenName nvarchar(256)
   ,@cn nvarchar(256)
   ,@l nvarchar(256)
   ,@streetAddress nvarchar(256)
   ,@userAccountControl int
   ,@strSQL varchar(7500)
   ,@dcName varchar(100)
   ,@ABC VARCHAR(1)
   ,@CurrentDate datetime
   SET @CurrentDate = getdate()
   TRUNCATE TABLE dbo.tblORG_AD_Users
   SET @strSQL = 'DECLARE DOMAIN_cursor CURSOR FOR
   SELECT name
   
   FROM OPENQUERY( ADSI,'''+ 'SELECT name
   FROM ''''LDAP://org''''''' + ')'
   + ' WHERE name like ''%.com.win'''IF (@pdcName is not null) BEGIN
   SET @strSQL = @strSQL + ' AND name = ''' + @pdcName + ''''
   END
   SET @strSQL = @strSQL + ' GROUP BY name'
   SET @strSQL = @strSQL + ' ORDER BY name'
   EXEC(@strSQL)
   OPEN DOMAIN_cursorFETCH NEXT FROM DOMAIN_cursor
   INTO @dcNameWHILE (@@FETCH_STATUS = 0) BEGINDECLARE ABC_cursor CURSOR FOR
   SELECT 'A'
   UNION
   SELECT 'B'
   UNION
   SELECT 'C'
   UNION
   SELECT 'D'
   UNION
   SELECT 'E'
   UNION
   SELECT 'F'
   UNION
   SELECT 'G'
   UNION
   SELECT 'H'
   UNION
   SELECT 'I'
   UNION
   SELECT 'J'
   UNION
   SELECT 'K'
   UNION
   SELECT 'L'
   UNION
   SELECT 'M'
   UNION
   SELECT 'N'
   UNION
   SELECT 'O'
   UNION
   SELECT 'P'
   UNION
   SELECT 'Q'
   UNION
   SELECT 'R'
   UNION
   SELECT 'S'
   UNION
   SELECT 'T'
   UNION
   SELECT 'U'
   UNION
   SELECT 'V'
   UNION
   SELECT 'W'
   UNION
   SELECT 'X'
   UNION
   SELECT 'Y'
   UNION
   SELECT 'Z'
   
   -- Get Alphabet letter
   
   OPEN ABC_cursor
   FETCH NEXT FROM ABC_cursor
   INTO @ABC
   
   WHILE (@@FETCH_STATUS = 0) BEGIN
    SET @strSQL = 'DECLARE ADSI_cursor CURSOR
    FOR SELECT sAMAccountName
     ,userPrincipalName
     ,adspath
     ,Department
     ,Title
     ,mail
     ,telephoneNumber
     ,sn
     ,givenName
     ,cn
     ,l
     ,streetAddress
     ,userAccountControl
    FROM OPENQUERY( ADSI,'''+ 'SELECT sAMAccountName
     ,userPrincipalName
     ,adspath
     ,Department
     ,Title
     ,mail
     ,telephoneNumber
     ,sn
     ,givenName
     ,cn
     ,l
     ,streetAddress
     ,userAccountControl'+ ' FROM ''''LDAP://'
     + @dcName + ''''''
     + ' WHERE objectClass=''''user'''''
     + ' AND objectCategory=''''Person'''''
     + ' AND mail=''''*'''''
     + ' AND sn=''''' + @ABC + '*'''''''
     + ')'
     
    EXEC(@strSQL)
    
    -- Fill table
    
    OPEN ADSI_cursor
    FETCH NEXT FROM ADSI_cursor
    INTO @sAMAccountName
     ,@userPrincipalName
     ,@adspath
     ,@Department
     ,@Title
     ,@mail
     ,@telephoneNumber
     ,@sn
     ,@givenName
     ,@cn
     ,@l
     ,@streetAddress
     ,@userAccountControl
     
    INSERT INTO dbo.tblORG_AD_Users(
      sAMAccountName
      ,userPrincipalName
      ,adspath
      ,Department
      ,Title
      ,mail
      ,telephoneNumber
      ,sn
      ,givenName
      ,cn
      ,l
      ,streetAddress
      ,userAccountControl
      ,loginName
      ,DateCreated)
    VALUES( @sAMAccountName
      ,@userPrincipalName
      ,@adspath
      ,@Department
      ,@Title
      ,@mail
      ,@telephoneNumber
      ,@sn
      ,@givenName
      ,@cn
      ,@l
      ,@streetAddress
      ,@userAccountControl
      ,replace(substring(@adspath,0,charindex('.org.win/',@adspath)),'LDAP://','') + '\' + @sAMAccountName
      ,@CurrentDate)
    WHILE @@FETCH_STATUS = 0 BEGIN
    FETCH NEXT FROM ADSI_cursor
    INTO @sAMAccountName
     ,@userPrincipalName
     ,@adspath
     ,@Department
     ,@Title
     ,@mail
     ,@telephoneNumber
     ,@sn
     ,@givenName
     ,@cn
     ,@l
     ,@streetAddress
     ,@userAccountControl
     
    INSERT INTO dbo.tblORG_AD_Users(
      sAMAccountName
      ,userPrincipalName
      ,adspath
      ,Department
      ,Title
      ,mail
      ,telephoneNumber
      ,sn
      ,givenName
      ,cn
      ,l
      ,streetAddress
      ,userAccountControl
      ,loginName
      ,DateCreated)
    VALUES( @sAMAccountName
      ,@userPrincipalName
      ,@adspath
      ,@Department
      ,@Title
      ,@mail
      ,@telephoneNumber
      ,@sn
      ,@givenName
      ,@cn
      ,@l
      ,@streetAddress
      ,@userAccountControl
      ,replace(substring(@adspath,0,charindex('.det.win/',@adspath)),'LDAP://','') + '\' + @sAMAccountName
      ,@CurrentDate)
    END
    CLOSE ADSI_cursor
    DEALLOCATE ADSI_cursor
    
    FETCH NEXT FROM ABC_cursor
    INTO @ABC
    END
    CLOSE ABC_cursor
    DEALLOCATE ABC_cursor
    FETCH NEXT FROM DOMAIN_cursor
    INTO @dcName
   
   END
   CLOSE DOMAIN_cursor
   DEALLOCATE DOMAIN_cursor
   END
   GO
  
  SET QUOTED_IDENTIFIER OFF
  GO
  
  SET ANSI_NULLS ON
  GO

If you find this script useful please donate generously.

Adding Trigger to all database tables

The following T-SQL view and stored procedure along with the script add (or modifying existing) 'FOR INSERT' triggers to all database tables:

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_NULLS ON
GO

PRINT 'drop view dbo.udvTriggersDefinitions'
GO
if exists (select * from dbo.sysobjects
where id = object_id(N'dbo.udvTriggersDefinitions')
and OBJECTPROPERTY(id, N'IsView') = 1)

drop view dbo.udvTriggersDefinitions
GO

PRINT 'CREATE VIEW dbo.udvTriggersDefinitions'
GO

/******************************************************************************
NAME: dbo.udvTriggersDefinitions

PURPOSE:

REVISIONS:

$Log$
Ver Date Author Description
******************************************************************************/
create view dbo.udvTriggersDefinitions
as

 SELECT TOP 100 PERCENT T.name AS TableName
   ,COL_NAME(T.object_id,1) pkID
   ,0 fuCounter
   ,NULL definition
   ,NULL bgnText
   ,NULL endText
 FROM (SELECT *
   FROM sys.objects SOTR
   WHERE type_desc LIKE 'SQL_TRIGGER') AS TR
   RIGHT OUTER JOIN
    (SELECT *
    FROM sys.objects AS SOT
    WHERE (type_desc LIKE 'USER_TABLE')) AS T
    ON TR.parent_object_id = T.object_id
   LEFT OUTER JOIN
   (SELECT object_id, definition
    FROM sys.sql_modules
   ) AS TS
   ON TR.object_id = TS.object_id
   WHERE TR.name IS NULL
   AND T.name NOT IN('dtproperties','sysdiagrams')
   UNION
   SELECT TOP 100 PERCENT TableName
    ,COL_NAME(object_id,1) pkID
    ,SUM(Flag) fuCounter
    ,NULL definition
    ,NULL bgnText
    ,NULL endText
   FROM(SELECT T.name AS TableName
    , TS.definition
    ,(CASE WHEN TS.definition Like '%FOR UPDATE%' THEN 1
   ELSE 0 END) Flag
    ,T.object_id
   FROM (SELECT *
     FROM sys.objects SOTR
     WHERE type_desc LIKE 'SQL_TRIGGER') AS TR
   RIGHT OUTER JOIN (SELECT *
        FROM sys.objects AS SOT
        WHERE (type_desc LIKE 'USER_TABLE')) AS T
   ON TR.parent_object_id = T.object_id
   LEFT OUTER JOIN (SELECT object_id, definition
        FROM sys.sql_modules
       ) AS TS
   ON TR.object_id = TS.object_id
   WHERE TS.definition IS NOT NULL) T
   GROUP BY TableName
     ,object_id
   HAVING SUM(Flag) = 0
   UNION SELECT TOP 100 PERCENT T.name AS TableName
     ,COL_NAME(T.object_id,1) pkID
     ,1 fuCounter
     ,TS.definition
     ,REPLACE(SUBSTRING(TS.definition,1,LEN(TS.definition) - CHARINDEX('DNE', REVERSE(TS.definition)) + 1 - 3),'''','''''') bgnText
     ,SUBSTRING(TS.definition,LEN(TS.definition) - CHARINDEX('DNE', REVERSE(TS.definition)) + 1 + 3,LEN(TS.definition)) endText
     FROM (SELECT *
       FROM sys.objects SOTR
       WHERE type_desc LIKE 'SQL_TRIGGER') AS TR
     RIGHT OUTER JOIN (SELECT *
          FROM sys.objects AS SOT
     WHERE (type_desc LIKE 'USER_TABLE')) AS T
     ON TR.parent_object_id = T.object_id
   LEFT OUTER JOIN (SELECT object_id, definition
        FROM sys.sql_modules
       ) AS TS
     ON TR.object_id = TS.object_id
   WHERE TS.definition Like '%FOR UPDATE%'
   ORDER BY TableName
GO

SET QUOTED_IDENTIFIER OFF
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_NULLS ON
GO

PRINT 'drop procedure dbo.udpUpdateAuditingFields'
GO

if exists (select * from dbo.sysobjects
where id = object_id(N'dbo.udpUpdateAuditingFields')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure dbo.udpUpdateAuditingFields
GO

PRINT 'CREATE PROC dbo.udpUpdateAuditingFields'

GO
/******************************************************************************
NAME: udpUpdateAuditingFields

PURPOSE:

REVISIONS:

$Log$

Ver Date Author Description
exec dbo.udpUpdateAuditingFields 'dbo.tblClients' --@TableName
 ,'test'  --@UserName
 ,'ClientID' --@ID_name
 ,5987   --@ID_value
*****************************************************************************/
CREATE PROC dbo.udpUpdateAuditingFields
 @TableName NVARCHAR(255)
 ,@UserName NVARCHAR(255)
 ,@ID_name NVARCHAR(255)
 ,@ID_value INT
AS
DECLARE  @Current_Date DATETIME
  ,@strSQL NVARCHAR(MAX)

BEGIN
 SET @Current_Date = getdate()
 SET @strSQL = 'UPDATE ' + @TableName
 SET @strSQL = @strSQL + ' SET ModifiedBy = ''' + @UserName
 SET @strSQL = @strSQL + ' '',ModifiedDate = ''' + CONVERT(NVARCHAR(30),@Current_Date)
 SET @strSQL = @strSQL + ''' WHERE '
 SET @strSQL = @strSQL + @ID_name + ' = ' + CONVERT(NVARCHAR(30),@ID_value)
 PRINT @strSQL

 EXEC(@strSQL)
 END
GO

SET QUOTED_IDENTIFIER OFF
GO

SET ANSI_NULLS ON
GO

DECLARE @schema nvarchar(255)
  ,@pkID nvarchar(255)
  ,@table_name nvarchar(255)
  ,@command NVARCHAR(4000)
  ,@definition NVARCHAR(4000)
  ,@fuCounter INT
  ,@bgnText nvarchar(3000);

 DECLARE crs CURSOR FOR
  SELECT   TableName
    ,pkID
    ,fuCounter
    ,[definition]
    ,bgnText
  FROM dbo.udvTriggersDefinitions

  OPEN crs
  FETCH NEXT FROM crs
  INTO @table_name
   ,@pkID
   ,@fuCounter
   ,@definition
   ,@bgnText;

  SET @Command = N'SET QUOTED_IDENTIFIER ON;'
  SET @Command = @Command + N' SET ANSI_NULLS ON;'
  SET @Command = @Command + N' DECLARE @strSQL NVARCHAR(4000)'
  SET @Command = @Command + N' ,@trgName NVARCHAR(255);'
  SET @Command = @Command + N' SET @trgName = ''dbo.T_' + @table_name + '_UTrig'';'
  SET @Command = @Command + N' PRINT ''DROP TRIGGER '' + @trgName;'
  SET @Command = @Command + N' IF OBJECT_ID (@trgName,''TR'') IS NOT NULL'
  SET @Command = @Command + N' BEGIN'
  SET @Command = @Command + N' SET @strSQL = ''DROP TRIGGER '' + @trgName;'
  SET @Command = @Command + N' EXEC(@strSQL);'
  SET @Command = @Command + N' END;'
  SET @Command = @Command + N' PRINT ''CREATE TRIGGER '' + @trgName;'
  SET @Command = @Command + N' /******************************************************************************'
  SET @Command = @Command + N' NAME: @trgName'
  SET @Command = @Command + N' PURPOSE: '
  SET @Command = @Command + N''
  SET @Command = @Command + N' REVISIONS:'
  SET @Command = @Command + N''
  SET @Command = @Command + N' $Log$'
  SET @Command = @Command + N''
  SET @Command = @Command + N' Ver Date Author Description'
  SET @Command = @Command + N' --------- ---------- --------------- ------------------------------------'
  SET @Command = @Command + N' ******************************************************************************/'

 IF @fuCounter = 0 BEGIN
   SET @Command = @Command + N' SET @strSQL = ''CREATE TRIGGER '' + @trgName'
   SET @Command = @Command + N' + '' ON '' + ''' + @table_name + ''
   SET @Command = @Command + N' FOR UPDATE'
   SET @Command = @Command + N' AS'
   SET @Command = @Command + N' BEGIN'
   SET @Command = @Command + N' DECLARE @ID_value INT'
   SET @Command = @Command + N' ,@USR NVARCHAR(255);'
   SET @Command = @Command + N' SELECT @ID_value = ' + @pkID + ' FROM Inserted;'
   SET @Command = @Command + N' EXEC dbo.udpUpdateAuditingFields ''''' +@table_name + ''''''
   SET @Command = @Command + N', @USR'
   SET @Command = @Command + N', ''''' + @pkID + ''''''
   SET @Command = @Command + N', @ID_value;'
   SET @Command = @Command + N' END'''
   SET @Command = @Command + N' EXEC(@strSQL);'
   END ELSE BEGIN
   SET @Command = @Command + N' SET @strSQL = ''' + @bgnText;
   SET @Command = @Command + N' BEGIN';
   SET @Command = @Command + N' DECLARE @ID_value INT'
   SET @Command = @Command + N' ,@USR NVARCHAR(255);'
   SET @Command = @Command + N' SELECT @ID_value = ' + @pkID + ' FROM Inserted;'
   SET @Command = @Command + N' EXEC dbo.udpUpdateAuditingFields ''''' +@table_name + ''''''
   SET @Command = @Command + N', @USR'
   SET @Command = @Command + N', ''''' + @pkID + ''''''
   SET @Command = @Command + N', @ID_value;'
   SET @Command = @Command + N' END; END'''
   SET @Command = @Command + N' EXEC(@strSQL);'
  END;

  SET @Command = @Command + N' SET QUOTED_IDENTIFIER OFF;'
  SET @Command = @Command + N' SET ANSI_NULLS ON;'
  WHILE @@FETCH_STATUS = 0
  BEGIN
  PRINT @Command;
  PRINT ''

  EXEC sp_executesql @Command;

  PRINT ''
  FETCH NEXT FROM crs
  INTO @table_name
   ,@pkID
   ,@fuCounter
   ,@definition
   ,@bgnText;

  SET @Command = N'SET QUOTED_IDENTIFIER ON;'
  SET @Command = @Command + N' SET ANSI_NULLS ON;'
  SET @Command = @Command + N' DECLARE @strSQL NVARCHAR(4000)'
  SET @Command = @Command + N' ,@trgName NVARCHAR(255);'
  SET @Command = @Command + N' SET @trgName = ''dbo.T_' + @table_name + '_UTrig'';'
  SET @Command = @Command + N' PRINT ''DROP TRIGGER '' + @trgName;'
  SET @Command = @Command + N' IF OBJECT_ID (@trgName,''TR'') IS NOT NULL'
  SET @Command = @Command + N' BEGIN'
  SET @Command = @Command + N' SET @strSQL = ''DROP TRIGGER '' + @trgName;'
  SET @Command = @Command + N' EXEC(@strSQL);'
  SET @Command = @Command + N' END;'
  SET @Command = @Command + N' PRINT ''CREATE TRIGGER '' + @trgName;'
  SET @Command = @Command + N' /******************************************************************************'
  SET @Command = @Command + N' NAME: @trgName'
  SET @Command = @Command + N' PURPOSE: '
  SET @Command = @Command + N''
  SET @Command = @Command + N' REVISIONS:'
  SET @Command = @Command + N''
  SET @Command = @Command + N' $Log$'
  SET @Command = @Command + N''
  SET @Command = @Command + N' Ver Date Author Description'
  SET @Command = @Command + N' --------- ---------- --------------- ------------------------------------'
  SET @Command = @Command + N' ******************************************************************************/'

  IF @fuCounter = 0 BEGIN
   SET @Command = @Command + N' SET @strSQL = ''CREATE TRIGGER '' + @trgName'
   SET @Command = @Command + N' + '' ON '' + ''' + @table_name + ''
   SET @Command = @Command + N' FOR UPDATE'
   SET @Command = @Command + N' AS'
   SET @Command = @Command + N' BEGIN'
   SET @Command = @Command + N' DECLARE @ID_value INT'
   SET @Command = @Command + N' ,@USR NVARCHAR(255);'
   SET @Command = @Command + N' SELECT @ID_value = ' + @pkID + ' FROM Inserted;'
   SET @Command = @Command + N' EXEC dbo.udpUpdateAuditingFields ''''' +@table_name + ''''''
   SET @Command = @Command + N', @USR'
   SET @Command = @Command + N', ''''' + @pkID + ''''''
   SET @Command = @Command + N', @ID_value;'
   SET @Command = @Command + N' END'''
   SET @Command = @Command + N' EXEC(@strSQL);'
   END ELSE BEGIN
   SET @Command = @Command + N' SET @strSQL = ''' + @bgnText;
   SET @Command = @Command + N' BEGIN';
   SET @Command = @Command + N' DECLARE @ID_value INT'
   SET @Command = @Command + N' ,@USR NVARCHAR(255);'
   SET @Command = @Command + N' SELECT @ID_value = ' + @pkID + ' FROM Inserted;'
   SET @Command = @Command + N' EXEC dbo.udpUpdateAuditingFields ''''' +@table_name + ''''''
   SET @Command = @Command + N', @USR'
   SET @Command = @Command + N', ''''' + @pkID + ''''''
   SET @Command = @Command + N', @ID_value;'
   SET @Command = @Command + N' END; END'''
   SET @Command = @Command + N' EXEC(@strSQL);'
  END;

  SET @Command = @Command + N' SET QUOTED_IDENTIFIER OFF;'
  SET @Command = @Command + N' SET ANSI_NULLS ON;'
 END;

 CLOSE crs;
 DEALLOCATE crs;

If you find this script useful please donate generously.

Adding Columns to all Tables

The following T-SQL script loops through all schemas and adds four record audit fields to all database tables:

 DECLARE  @schema nvarchar(255)
   ,@command NVARCHAR(2000)
 DECLARE crs CURSOR FOR
  SELECT name + '.INFORMATION_SCHEMA'
 FROM sys.Databases
 WHERE name NOT IN ('master','tempdb','model','msdb'
  ,'ReportServer','ReportServerTempDB','Sharepoint_ReportServer'
  ,'Sharepoint_ReportServerTempDB','BIDocumenter')
  
 OPEN crsFETCH NEXT FROM crs
 INTO @schema
 
 SET @Command = N'DECLARE @TempTable TABLE (tblName nvarchar(255));
 
 DECLARE @cmd NVARCHAR(2000);
 DECLARE @tblName nvarchar(255);

 INSERT INTO @TempTable
 SELECT TABLE_CATALOG + ''.'' + TABLE_SCHEMA + ''.['' + TABLE_NAME + '']''
 FROM ' + @schema + '.TABLES
 WHERE TABLE_TYPE = ''BASE TABLE'' AND TABLE_NAME NOT IN (''sysdiagrams'',''dtproperties'')
 ORDER BY TABLE_NAME;

 DECLARE c CURSOR FOR SELECT * FROM @TempTable;OPEN c;
 FETCH NEXT FROM c INTO @tblName;

 WHILE @@FETCH_STATUS = 0 BEGIN
  SET @cmd = ''ALTER TABLE '''+ ' + @tblName + '' ADD Created_By NVARCHAR(255) NULL DEFAULT (user_name());'';
  PRINT @cmd;
  EXEC sp_executesql @cmd;
  SET @cmd = ''ALTER TABLE ''' + ' + @tblName + '' ADD Date_Created DATETIME NULL DEFAULT (GETDATE());'';
  PRINT @cmd;
  EXEC sp_executesql @cmd;
  SET @cmd = ''ALTER TABLE ''' + ' + @tblName + '' ADD Modified_By NVARCHAR(255) NULL;'';
  PRINT @cmd;
  EXEC sp_executesql @cmd;
  SET @cmd = ''ALTER TABLE ''' + ' + @tblName + '' ADD Date_Modified DATETIME NULL;'';
  PRINT @cmd;
  EXEC sp_executesql @cmd;
  FETCH NEXT FROM c INTO @tblName;
 END
 CLOSE c;
 DEALLOCATE c;'
 
 WHILE @@FETCH_STATUS = 0 BEGIN
  PRINT @Command
  PRINT ''
  
  EXEC sp_executesql @Command
  
  PRINT ''
  
  FETCH NEXT FROM crs
  INTO @schema
  SET @Command = N'DECLARE @TempTable TABLE (tblName nvarchar(255));
  
  DECLARE @cmd NVARCHAR(2000);
  DECLARE @tblName nvarchar(255);

  INSERT INTO @TempTable    
  SELECT TABLE_CATALOG + ''.'' + TABLE_SCHEMA + ''.['' + TABLE_NAME + '']''
  FROM ' + @schema + '.TABLES
  WHERE TABLE_TYPE = ''BASE TABLE'' AND TABLE_NAME NOT IN (''sysdiagrams'',''dtproperties'')
  ORDER BY TABLE_NAME;

  DECLARE c CURSOR FOR SELECT * FROM @TempTable;OPEN c;
  FETCH NEXT FROM c INTO @tblName;

  WHILE @@FETCH_STATUS = 0 BEGIN
   SET @cmd = ''ALTER TABLE ''' + ' + @tblName + '' ADD CreatedBy NVARCHAR(255) NULL (user_name());'';
   PRINT @cmd;
   EXEC sp_executesql @cmd;
   SET @cmd = ''ALTER TABLE ''' + ' + @tblName + '' ADD CreatedBDate DATETIME NULL DEFAULT (GETDATE());'';
   PRINT @cmd;
   EXEC sp_executesql @cmd;
   SET @cmd = ''ALTER TABLE ''' + ' + @tblName + '' ADD ModifieddBy NVARCHAR(255) NULL;'';
   PRINT @cmd;
   EXEC sp_executesql @cmd;
   SET @cmd = ''ALTER TABLE ''' + ' + @tblName + '' ADD ModifiedDate DATETIME NULL;'';
   PRINT @cmd;
   EXEC sp_executesql @cmd;
   FETCH NEXT FROM c INTO @tblName;
  END

  CLOSE c;
  DEALLOCATE c;'
 END
 CLOSE crs;
 DEALLOCATE crs;

If you find this script useful please donate generously.