Thursday, March 31, 2011

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_cursor

FETCH NEXT FROM DOMAIN_cursor
INTO @dcName

WHILE (@@FETCH_STATUS = 0) BEGIN

DECLARE 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.