背景
我正在创建一些sql来协助安全审计;这将从各种系统数据库和Active Directory获取安全信息,并将生成所有异常的列表(即帐户在一个系统中关闭但不在其他系统中关闭的情况.
现行守则
if not exists(select 1 from sys.servers where name = 'ADSI')
EXEC sp_addlinkedserver 'ADSI','Active Directory Services 2.5','ADSDSOObject','adsdatasource'
SELECT sAMAccountName,displayName,givenName,sn,isDeleted --,lastlogonTimestamp --,lastlogon (Could not convert the data value due to reasons other than sign mismatch or overflow.)
FROM OPENQUERY(ADSI,'SELECT sAMAccountName,isDeleted
FROM ''LDAP://DC=myDomain,DC=myCompany,DC=com''
WHERE objectCategory = ''Person''
AND objectClass = ''user''
AND memberOf = ''CN=mySecurityGroup,OU=Security Groups,OU=UK,DC=myDomain,DC=com''
')
order by sAMAccountName
问题/问题
我希望这段代码能够递归地工作;即,如果用户是作为指定组成员的组的成员,则也应包括它们(对于完整层次结构).有谁知道如何通过sql做到这一点？
UPDATE
我现在已经解决了一些问题(与引用的问题无关,但我还有其他一些问题).
> lastlogon抛出了一个错误.这是因为服务器版本是x86.使用x64数据库解决了该问题.
> lastlogon作为数字返回.添加了一些代码将其转换为DateTime2.
>通过使OpenQuery本身动态,我能够将组名从硬编码字符串中移出,因此在OpenQuery的上下文中,生成的字符串看起来是静态的.
..
--create linked server
if not exists(select 1 from sys.servers where name = 'ADSI')
begin
--EXEC sp_addlinkedserver 'ADSI','adsdatasource'
EXEC master.dbo.sp_addlinkedserver 'ADSI','Active Directory Service Interfaces','adsdatasource'
EXEC master.dbo.sp_serveroption @server=N'ADSI',@optname=N'collation compatible',@optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'ADSI',@optname=N'data access',@optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'ADSI',@optname=N'dist',@optname=N'pub',@optname=N'rpc',@optname=N'rpc out',@optname=N'sub',@optname=N'connect timeout',@optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'ADSI',@optname=N'collation name',@optvalue=null
EXEC master.dbo.sp_serveroption @server=N'ADSI',@optname=N'lazy schema validation',@optname=N'query timeout',@optname=N'use remote collation',@optname=N'remote proc transaction promotion',@optvalue=N'true'
end
declare @path nvarchar(1024) = 'DC=myDomain,DC=com'
declare @groupCN nvarchar(1024) = 'CN=My Security Group,' + @path,@sql nvarchar(max)
--construct the query we send to AD
set @sql = '
SELECT sAMAccountName,isDeleted,lastlogon
FROM ''LDAP://' + replace(@path,'''','''''') + '''
WHERE objectCategory = ''Person''
AND objectClass = ''user''
AND memberOf = ''' + replace(@groupCN,'''''') + '''
'
--now wrap that query in the outer query
set @sql = 'SELECT sAMAccountName,case
when cast([lastlogon] as bigint) = 0 then null
else dateadd(mi,(cast([lastlogon] as bigint) / 600000000),cast(''1601-01-01'' as datetime2))
end Lastlogon
FROM OPENQUERY(ADSI,''' + replace(@sql,'''''') + ''')
order by sAMAccountName'
--now run it
exec(@sql)
解决方法
虽然这是一个老帖子,谷歌仍然喜欢把它扔到结果的顶部,所以当我努力解决同样的问题时,我想发布我的发现/解决方案,并将信用归功于Riverway让我进入正确的轨道上.
创建存储过程：
CREATE PROCEDURE [dbo].[GetLdapUserGroups]
(
@LdapUsername NVARCHAR(max)
)
AS
BEGIN
DECLARE @Query NVARCHAR(max),@Path NVARCHAR(max)
SET @Query = '
SELECT @Path = distinguishedName
FROM OPENQUERY(ADSI,''
SELECT distinguishedName
FROM ''''LDAP://DC=DOMAIN,DC=COM''''
WHERE
objectClass = ''''user'''' AND
sAMAccountName = ''''' + @LdapUsername + '''''
'')
'
EXEC SP_EXECUTEsql @Query,N'@Path NVARCHAR(max) OUTPUT',@Path = @Path OUTPUT
SET @Query = '
SELECT cn AS [LdapGroup]
FROM OPENQUERY (ADSI,''<LDAP://DOMAIN.COM>;
(&(objectClass=group)(member:1.2.840.113556.1.4.1941:= ' + @Path + '));
cn,adspath;subtree'')
ORDER BY cn;
'
EXEC SP_EXECUTEsql @Query
END
DECLARE @UserGroup table (LdapGroup nvarchar(max)) INSERT INTO @UserGroup exec Datamart.dbo.GetLdapUserGroups @LdapUser
然后我使用哈希表将AD组正确匹配到sql数据以及最终用户应该看到的内容.
DECLARE @RptPermissions table (ldapGroup nvarchar(max),scholarshipCode nvarchar(50),gender nvarchar(2))
INSERT INTO @RptPermissions VALUES('EMP_Enrollment_Admissions','ALL','MF')
就我而言,我使用它来提取SSRS用户变量并将其传递给查询,以便根据AD组成员资格选择记录.
;WITH CTE_Permissions AS
(
SELECT
p.scholarshipCode,p.gender
FROM @UserGroup AS g
JOIN @RptPermissions AS p ON
g.ldapGroup = p.ldapGroup
)
…稍后在查询中
JOIN CTE_Permissions AS p ON
s.SCHOLARSHIP_ID = p.scholarshipCode
OR p.scholarshipCode = 'ALL'
希望这可以帮助.
